Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports AppsUtility.EncDec
Imports System.IO

Public Class ARdataLogic
    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString"))
    Public Function RetrieveUserName(ByVal uid As String) As DataSet
        'SELECT * FROM USERPROFILE where USERID = X.DocsMan
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsDBNull(uid) Then
            strsql = "select UserName " & _
                   "from USERPROFILE "
        Else
            strsql = "select UserName " & _
              "from USERPROFILE where UserID = " & uid

        End If

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
            'If ds.Tables("CUSTOMER") Then
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        RetrieveUserName = ds
    End Function

    Public Function GetCustomers(ByVal WhereClause As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * " & _
                "from Customer " & WhereClause & " order by CusName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomers = ds
    End Function
    Public Function GetProductDetails(ByVal ProductID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Product where ProductID=" & ProductID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductDetails = ds
    End Function
    Public Function GetProductDetails2(ByVal ProductCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Product where ProductCode='" & ProductCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductDetails2 = ds
    End Function
    Public Function ProductCodeExist(ByVal CompanyID As Integer, ByVal ProductCode As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) ProdCount from Product where ProductCode='" & ProductCode & "' and CompanyCode=" & CompanyID
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ProdCount As Integer
        ProdCount = CInt(sqlCmd.ExecuteScalar)
        sqlCon.Close()
        If ProdCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function SupplierProductCodeExist(ByVal CompanyID As Integer, ByVal ProductCode As String, ByVal SupID As Integer) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) ProdCount from Product a inner join ProductSupplier b " & _
        "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID & _
        " where a.ProductCode='" & ProductCode & "'"
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ProdCount As Integer
        ProdCount = CInt(sqlCmd.ExecuteScalar)
        sqlCon.Close()
        If ProdCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function SearchProduct(ByVal CompanyID As Integer, ByVal SearchColumn As String, ByVal SearchString As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If SearchColumn.ToUpper <> "PRODUCTBARCODE" Then
            strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE,MANUFPRODUCTCODE," & _
                    "B.SHORTDESCRIPTIONE,B.LONGDESCRIPTIONE,B.FIRSTPRICE " & _
                    "FROM BARCODE A,PRODUCT B " & _
                    "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                    "AND B." & SearchColumn & " like '" & SearchString & "%' order by B." & SearchColumn
            If SearchColumn.ToUpper = "SHORTDESCRIPTIONE" Then
                strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE,MANUFPRODUCTCODE," & _
                        "B.SHORTDESCRIPTIONE,B.LONGDESCRIPTIONE,B.FIRSTPRICE " & _
                        "FROM BARCODE A,PRODUCT B " & _
                        "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                        "AND B." & SearchColumn & " like '%" & SearchString & "%' order by B." & SearchColumn
            End If
        Else
            strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE,MANUFPRODUCTCODE," & _
                    "B.SHORTDESCRIPTIONE,B.LONGDESCRIPTIONE,B.FIRSTPRICE " & _
                    "FROM BARCODE A,PRODUCT B " & _
                    "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                    "AND A." & SearchColumn & " like '" & SearchString & "%' order by A." & SearchColumn
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        SearchProduct = ds
    End Function
    Public Function GetCustomerProductDetails(ByVal CompanyID As Integer, ByVal CustomerID As Integer, ByVal ProductCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ShortDescriptione,a.LongDescriptione,a.ProductCode," & _
                "a.FirstPrice,a.QtyOnHand from Product a inner join CustomerProduct b " & _
                "on a.ProductID=b.ProductID and b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & _
                " and b.CustomerProdCode='" & ProductCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomerProductDetails = ds
    End Function
    Public Function GetCustomerProducts(ByVal CompanyID As Integer, ByVal CustomerID As Integer, ByVal WhereClause As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
            "b.CustomerProdCode,a.ShortDescriptione,a.FirstPrice,a.SecondPrice,a.ThirdPrice " & _
            "from Product a LEFT OUTER join CustomerProduct b on " & _
            "a.ProductID = b.ProductID " & _
            "WHERE b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & _
            " " & WhereClause & _
            "union all " & _
            "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
            "space(30) CustomerProdCode,a.ShortDescriptione,a.FirstPrice,a.SecondPrice,a.ThirdPrice " & _
            "from Product a where ProductID NOT IN " & _
            "(select a.ProductID from Product a LEFT OUTER join CustomerProduct b on " & _
            "a.ProductID=b.ProductID " & _
            "where b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & " " & WhereClause & ") " & WhereClause

        ' "a.CompanyCode=" & CompanyID & " " & WhereClause & " and ProductID NOT IN " & _

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomerProducts = ds
    End Function
    Public Function GetProductWarehouseBins(ByVal WarehouseID As Integer, ByVal WhereClause As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
        '    "b.BinName,a.ShortDescriptione,a.FirstPrice,PresentQty " & _
        '    "from Product a LEFT OUTER join ProductWarehouseBin b on " & _
        '    "a.ProductID = b.ProductID " & _
        '    "left outer join ShowroomROL c on a.ProductID=c.ProductID " & _
        '    "WHERE b.WarehouseID=" & WarehouseID & " and c.ShowroomID=" & WarehouseID & _
        '    " " & WhereClause & _
        '    "union all " & _
        '    "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
        '    "space(15) BinName,a.ShortDescriptione,a.FirstPrice,PresentQty " & _
        '    "from Product a " & _
        '    "left outer join ShowroomROL c on a.ProductID=c.ProductID " & _
        '    "where 1=1  And c.ShowroomID = " & WarehouseID & _
        '    "" & WhereClause & " and a.ProductID NOT IN " & _
        '    "(select a.ProductID from Product a LEFT OUTER join ProductWarehouseBin b on " & _
        '    "a.ProductID=b.ProductID " & _
        '    "where b.WarehouseID=" & WarehouseID & " " & WhereClause & ")"
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
            "b.BinName,a.ShortDescriptione,a.FirstPrice,PresentQty " & _
            "from Product a LEFT OUTER join ProductWarehouseBin b on " & _
            "a.ProductID = b.ProductID " & _
            "left outer join ShowroomROL c on a.ProductID=c.ProductID " & _
            "WHERE b.WarehouseID=" & WarehouseID & " and c.ShowroomID=" & WarehouseID & _
            " " & WhereClause & _
            " union all " & _
            "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
            "space(15) BinName,a.ShortDescriptione,a.FirstPrice,0 PresentQty " & _
            "from Product a " & _
            " where  a.ProductID NOT IN " & _
            "(select b.ProductID from ProductWarehouseBin b " & _
            "where b.WarehouseID=" & WarehouseID & ") " & WhereClause & _
            " union all " & _
            "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
            "space(15) BinName,a.ShortDescriptione,a.FirstPrice,0 PresentQty " & _
            "from Product a " & _
            " where  a.ProductID NOT IN " & _
            "(select b.ProductID from ShowroomROL b " & _
            "where b.ShowroomID=" & WarehouseID & ") " & WhereClause & _
            " order by a.ProductCode"


        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductWarehouseBins = ds
    End Function
    Public Function GetSupplierProducts(ByVal CompanyID As Integer, ByVal SupplierID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join ProductSupplier b " & _
                "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSupplierProducts = ds
    End Function
    Public Function SearchCustomerProducts(ByVal CompanyID As Integer, ByVal CustomerID As Integer, ByVal SearchColumn As String, ByVal SearchString As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,b.CustomerProdCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join CustomerProduct b " & _
                "on a.ProductID=b.ProductID and b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & _
                " where " & SearchColumn & " like '" & SearchString & "%' order by " & SearchColumn
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        SearchCustomerProducts = ds
    End Function
    Public Function SearchSupplierProducts(ByVal CompanyID As Integer, ByVal SupplierID As Integer, ByVal SearchColumn As String, ByVal SearchString As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join ProductSupplier b " & _
                "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID & _
                " where " & SearchColumn & " like '" & SearchString & "%' order by " & SearchColumn
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        SearchSupplierProducts = ds
    End Function
    Public Function GetBarCodeDetails(ByVal CompanyID As Integer, ByVal BarCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,ProductBarCode,b.ProductCode," & _
                    "b.ProductType,b.ShortDescriptionE,b.ShortDescriptionA," & _
                    "b.FirstPrice,b.SecondPrice,b.SaleUnit,b.ActiveFlag," & _
                    "b.OnPromotionFlag,b.KitFlag,b.WhProductFlag " & _
                    "from BarCode a,Product b " & _
                    "where a.ProductBarCode='" & BarCode & "' and a.ProductID=b.ProductID"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBarCodeDetails = ds
    End Function
    Public Function GetBarCodes(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE," & _
                "B.SHORTDESCRIPTIONE,B.FIRSTPRICE " & _
                "FROM BARCODE A,PRODUCT B " & _
                "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                " ORDER BY B.SHORTDESCRIPTIONE"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBarCodes = ds
    End Function
    Public Function GetGroupDetails(ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from CustomerGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetGroupDetails = ds
    End Function
    Public Function GetDocDetails(ByVal DocID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from FasDoc where ID=" & DocID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetDocDetails = ds
    End Function
    Public Function GetAssetGroupDetails(ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from AssetGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetGroupDetails = ds
    End Function
    Public Function GetProductBarCodes(ByVal ProductID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from BarCode where ProductID=" & ProductID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("BARCODE")
        Try
            da.Fill(ds, "BARCODE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductBarCodes = ds
    End Function
    Public Function GetInvoiceLine(ByVal ProductBarCode As String, ByVal InvNo As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If InvNo.Trim <> "" Then
            strsql = "select * from InvData where InvBarCode='" & ProductBarCode & "' and InvNo='" & InvNo & "'"
        Else
            strsql = "select a.InvNo,c.InvDateTime,b.DepartmentName,InvDesc,InvQty,InvPrice,InvDisp,InvDisa,InvLtot from invdata a, Department b,invhead c " & _
                    "where a.InvID = c.InvID And b.DepartmentID = c.InvShowroomID " & _
                    "and a.InvBarCode='" & ProductBarCode & "' and DateDiff(day,InvDateTime,GetDate())<=30 " & _
                    "order by c.InvDateTime desc"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVLINE")
        Try
            da.Fill(ds, "INVLINE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceLine = ds
    End Function
    Public Function GetCustomerGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GroupID,a.GroupName " & _
                    "from CustomerGroup a " & _
                    " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMERGROUPS")
        Try
            da.Fill(ds, "CUSTOMERGROUPS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("GroupID") = 0
        dRow("GroupName") = "ALL GROUPS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetCustomerGroups = ds
    End Function
    Public Function GetDesignations() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.DesignationID,a.DesignationName " & _
                    "from EmployeeDesignation a " & _
                    " order by DesignationName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("DESIGNATION")
        Try
            da.Fill(ds, "DESIGNATION")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("DesignationID") = 0
        dRow("DesignationName") = "ALL DESIGNATION"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetDesignations = ds
    End Function
    Public Function GetNationalities() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.NationalityID,a.NationalityName " & _
                    "from Nationality a " & _
                    " order by NationalityName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("NATIONALITY")
        Try
            da.Fill(ds, "NATIONALITY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("NationalityID") = 0
        dRow("NationalityName") = "ALL NATIONALITY"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetNationalities = ds
    End Function
    Public Function GetNationGroup(ByVal NationID As Integer) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select NationalityGroup from Nationality " & _
        " where NationalityID=" & NationID
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ngroup As Integer
        ngroup = CInt(sqlCmd.ExecuteScalar)
        sqlCon.Close()
        Return ngroup
    End Function
    Public Function GetNextCardNo(ByVal ThisCompany As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select NextCardNo from Company with (NoLock) where CompanyID=" & ThisCompany
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = CStr(rdr("NextCardNo"))
        End While
        Dim nlen As Integer
        nlen = NextInv.Trim.Length
        Dim lzero As String = "000000"
        If nlen < 6 Then
            NextInv = lzero.Substring(1, 6 - nlen) & NextInv
        End If
        NextInv = "P" + NextInv
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetGovernorates() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GovernorateID,a.GovernorateName " & _
                    "from Governorate a " & _
                    " order by GovernorateName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GOVERNORATE")
        Try
            da.Fill(ds, "GOVERNORATE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("GovernorateID") = 0
        dRow("GovernorateName") = "ALL GOVERNORATE"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetGovernorates = ds
    End Function
    Public Function Promoschemes() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select id,promo,offer from POSPromoScheme " & _
                    " where status = 'ACTIVE'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CITY")
        Try
            da.Fill(ds, "CITY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("id") = 0
        dRow("promo") = "SELECT SCHEME"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        Promoschemes = ds
    End Function
    Public Function GetDiscountInfo(ByVal ids As String) As String
        'If IsNumeric(CInt(ids)) Then

        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select offer from POSPromoScheme " & _
                    " where id = " & ids
        sqlCon.Open()
        'MessageBox.Show(strsql)
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CITY")
        Try
            da.Fill(ds, "CITY")
            If ds.Tables("CITY").Rows.Count <> 0 Then
                Dim s As String = ds.Tables("CITY").Rows(0)(0).ToString()
                GetDiscountInfo = s
            End If

        Catch ex As SqlException
            'MsgBox(ex.ToString())
        End Try

        'End If
    End Function
    Public Function GetCities() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.AreaID,a.AreaName " & _
                    "from Area a " & _
                    " order by AreaName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CITY")
        Try
            da.Fill(ds, "CITY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("AreaID") = 0
        dRow("AreaName") = "ALL CITY"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetCities = ds
    End Function
    Public Function GetCitiesOfGovernorate(ByVal GovernorateID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If GovernorateID > 0 Then
            strsql = "select a.AreaID,a.AreaName " & _
                     "from Area a where GovernorateID=" & GovernorateID & _
                     " order by AreaName"
        Else
            strsql = "select a.AreaID,a.AreaName " & _
                    "from Area a order by AreaName"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CITY")
        Try
            da.Fill(ds, "CITY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("AreaID") = 0
        dRow("AreaName") = "ALL CITY"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetCitiesOfGovernorate = ds
    End Function
    Public Function GetAssetGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GroupID,a.GroupName " & _
                    "from AssetGroup a " & _
                    " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSETGROUPS")
        Try
            da.Fill(ds, "ASSETGROUPS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("GroupID") = 0
        dRow("GroupName") = "ALL GROUPS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetAssetGroups = ds
    End Function
    Public Function GetAssetLocationCode(ByVal code As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.AssetACcode " & _
                    "from AssetLocation a where AssetLocationID=" & code
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSETLOCATIONS")
        Try
            da.Fill(ds, "ASSETLOCATIONS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try

        sqlCon.Close()
        Return ds.Tables("ASSETLOCATIONS").Rows(0)(0).ToString()
    End Function
    Public Function GetAssetLocations() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.AssetLocationID,a.AssetLocationName " & _
                    "from AssetLocation a " & _
                    " order by AssetLocationName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSETLOCATIONS")
        Try
            da.Fill(ds, "ASSETLOCATIONS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("AssetLocationID") = 0
        dRow("AssetLocationName") = "ALL LOCATIONS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetAssetLocations = ds
    End Function
    Public Function GetAllShowrooms() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select b.CompanyID,b.CompanyName,a.DepartmentID,a.DepartmentName from Department a, Company b " & _
                 "where a.CompanyID=b.CompanyID order by CompanyName,DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllShowrooms = ds
    End Function
    Public Function GetAllCompany() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CompanyID,CompanyName from Company " & _
                 "order by CompanyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllCompany = ds
    End Function
    Public Function GetCompanyCrLimitPrompt() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CompanyID,CompanyName,0 CreditLimit,0 ValidCompany from Company " & _
                 "order by CompanyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyCrLimitPrompt = ds
    End Function
    Public Function GetCompanyShowrooms(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select DepartmentID,DepartmentName from Department " & _
                 "where CompanyID=" & CompanyID & " and ShowroomFlag=1 order by DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("DepartmentID") = 0
        dRow("DepartmentName") = "ALL SHOWROOMS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetCompanyShowrooms = ds
    End Function
    Public Function GetCompanySalesmen(ByVal CompanyID As Integer, ByVal DepartmentID As Integer, Optional ByVal ShowAll As Integer = 1) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select SalesmanID,SalesmanName from Salesman " & _
                 "where DepartmentID=" & DepartmentID & " and CompanyID=" & CompanyID & " order by SalesmanName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SALESMAN")
        Try
            da.Fill(ds, "SALESMAN")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        If ShowAll = 1 Then
            Dim dRow As DataRow
            dRow = ds.Tables(0).NewRow()
            dRow("SalesmanID") = 0
            dRow("SalesmanName") = "ALL SALESMEN"
            ds.Tables(0).Rows.Add(dRow)
        End If
        sqlCon.Close()
        GetCompanySalesmen = ds
    End Function
    Public Function GetShowroomsRol(ByVal CompanyID As Integer, ByVal ProdID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.DepartmentID,a.DepartmentName,isnull(b.MinimumQty,0) MinimumQty," & _
                 "isnull(b.PresentQty,0) PresentQty from Department a " & _
                "left outer join ShowroomRol b on a.DepartmentID=b.ShowroomID " & _
                "where a.CompanyID=" & CompanyID & " and a.ShowroomFlag = 1 and b.ProductID=" & ProdID & _
                " union all " & _
                "select a.DepartmentID,a.DepartmentName,0 MinimumQty,0 PresentQty " & _
                "from Department a where a.CompanyID=" & CompanyID & " and a.ShowroomFlag=1 " & _
                "and a.DepartmentID not in " & _
                "(select a.DepartmentID from Department a " & _
                "left outer join ShowroomRol b on a.DepartmentID=b.ShowroomID " & _
                "where a.CompanyID=" & CompanyID & " and a.ShowroomFlag = 1 and b.ProductID=" & ProdID & ") " & _
                "order by a.DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShowroomsRol = ds
    End Function
    Public Function GetProductSupplierPrompt(ByVal CompanyID As Integer, ByVal ProdID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID,a.SupName," & _
                "isnull(b.ValidSup,0) ValidSup " & _
                "from Supplier a left outer join ProductSupplier b " & _
                "on a.SupID=b.SupID where a.CompanyID=" & CompanyID & " and  " & _
                "b.ProdID=" & ProdID & " " & _
                "union all " & _
                "select a.SupID,a.SupName,0 ValidSup " & _
                "from Supplier a where a.CompanyID=3 " & _
                "and a.SupID not in " & _
                "(select a.SupID from Supplier a left outer join ProductSupplier b " & _
                "on a.SupID=b.SupID where a.CompanyID=" & CompanyID & " and  " & _
                "b.ProdID=" & ProdID & ") " & _
                "order by a.SupName"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductSupplierPrompt = ds
    End Function
    Public Function GetCompanySuppliers(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID,a.SupName " & _
                "from Supplier a,CompanySupplier b " & _
                "where b.CompanyID=" & CompanyID & " and a.SupID=b.SupID " & _
                "order by a.SupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanySuppliers = ds
    End Function
    Public Function GetCompanyCustomers(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.CusID,a.CusName,a.CusAdr1,a.CusAdr2,a.CusDAdr1,a.CusDAdr2 " & _
                "from Customer a,CompanyCustomer b " & _
                "where b.CompanyID=" & CompanyID & " and a.CusID=b.CusID " & _
                "order by a.CusName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyCustomers = ds
    End Function
    Public Function GetCustomersByCompany(ByVal CompanyID As Integer, ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If GroupID = 0 Then
            strsql = "select a.*,c.Salesman " & _
                    "from CompanyCustomer b,Customer a " & _
                    "left outer join Salesman c on a.SalesmanID=c.SalesmanID " & _
                    "where b.CompanyID=" & CompanyID & " and a.CusID=b.CusID " & _
                    "order by a.CusName"
        Else
            strsql = "select a.*,c.Salesman " & _
                    "from CompanyCustomer b ,Customer a " & _
                    "left outer join Salesman c on a.SalesmanID=c.SalesmanID " & _
                    "where b.CompanyID=" & CompanyID & " and a.CusID=b.CusID " & _
                    "and a.CusGroupID=" & GroupID & " " & _
                    "order by a.CusName"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomersByCompany = ds
    End Function
    Public Function GetPcpMainMembers(ByVal NationalityFlag As Integer, ByVal Status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If NationalityFlag = 0 Then
            strsql = "select a.*,b.NationalityName,c.GovernorateName,d.AreaName " & _
                    "from PcpMainMember a,Nationality b,Governorate c,Area d " & _
                    "where a.NationalityID=b.NationalityID and a.GovernorateID=c.GovernorateID and a.CityID=d.AreaID " & _
                    "and ltrim(rtrim(a.Status))='" & Status & "' order by a.PchCardNo"
        Else
            strsql = "select a.*,b.NationalityName,c.GovernorateName,d.AreaName " & _
                    "from PcpMainMember a,Nationality b,Governorate c,Area d " & _
                    "where a.NationalityID=b.NationalityID and a.GovernorateID=c.GovernorateID and a.CityID=d.AreaID " & _
                    "and ltrim(rtrim(a.Status))='" & Status & "' and b.NationalityGroup=" & NationalityFlag & " order by a.PchCardNo"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpMainMembers = ds
    End Function
    Public Function GetPcpMembersForEventEmail(ByVal KuwaitiFlag As Integer, ByVal ArabFlag As Integer, _
                ByVal EuroFlag As Integer, ByVal AsianFlag As Integer, ByVal NonFlag As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim InClause As String = ""
        If KuwaitiFlag = 1 Then
            InClause = "1 "
        End If
        If ArabFlag = 1 Then
            InClause += "2 "
        End If
        If EuroFlag = 1 Then
            InClause += "3 "
        End If
        If AsianFlag = 1 Then
            InClause += "4"
        End If
        If Right(InClause, 1) = " " Then
            InClause = InClause.Substring(0, Len(InClause) - 1)
        End If
        InClause = InClause.Replace(" ", ",")
        If InClause.Trim <> "" Then
            If NonFlag = 1 Then
                strsql = "Select FirstName+' '+SecondName+' '+FamilyName CusName,Email,NationalityName " & _
                        "from PcpMainMember a,Nationality b " & _
                        "where a.NationalityID=b.NationalityID  " & _
                        "and ltrim(rtrim(a.Status))='ACTIVE' and b.NationalityGroup in (" & InClause & ") " & _
                        "and isnull(Email,'')<>'' " & _
                        "union all " & _
                        "Select CustomerName CusName,EmailAddress Email,'Non-Member' NationalityName " & _
                        "from PcpNonMember where isnull(EmailAddress,'')<>'' "
            Else
                strsql = "Select FirstName+' '+SecondName+' '+FamilyName CusName,Email,NationalityName " & _
                        "from PcpMainMember a,Nationality b " & _
                        "where a.NationalityID=b.NationalityID  " & _
                        "and isnull(Email,'')<>'' " & _
                        "and ltrim(rtrim(a.Status))='ACTIVE' and b.NationalityGroup in (" & InClause & ") "
            End If
        Else
            If NonFlag = 1 Then
                strsql = "Select CustomerName CusName,EmailAddress Email,'Non-Member' NationalityName " & _
                        "from PcpNonMember where isnull(EmailAddress,'')<>'' "
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpMembersForEventEmail = ds
    End Function
    Public Function GetPcpMembersForTargetedEmail(ByVal KuwaitiFlag As Integer, ByVal ArabFlag As Integer, _
            ByVal EuroFlag As Integer, ByVal AsianFlag As Integer, _
            ByVal Group1Flag As Integer, ByVal Group2Flag As Integer, ByVal Group3Flag As Integer, _
            ByVal Group4Flag As Integer, ByVal Group5Flag As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim InClause As String = ""
        If KuwaitiFlag = 1 Then
            InClause = "1 "
        End If
        If ArabFlag = 1 Then
            InClause += "2 "
        End If
        If EuroFlag = 1 Then
            InClause += "3 "
        End If
        If AsianFlag = 1 Then
            InClause += "4"
        End If
        If Right(InClause, 1) = " " Then
            InClause = InClause.Substring(0, Len(InClause) - 1)
        End If
        InClause = InClause.Replace(" ", ",")
        Dim WhereClause As String = ""
        If Group1Flag <> 0 Then
            WhereClause += " Group1Code=" & Group1Flag
        End If
        If Group2Flag <> 0 Then
            If WhereClause <> "" Then
                WhereClause += " and Group2Code=" & Group2Flag
            Else
                WhereClause += " Group2Code=" & Group2Flag
            End If
        End If
        If Group3Flag <> 0 Then
            If WhereClause <> "" Then
                WhereClause += " and Group3Code=" & Group3Flag
            Else
                WhereClause += " Group3Code=" & Group3Flag
            End If
        End If
        If Group4Flag <> 0 Then
            If WhereClause <> "" Then
                WhereClause += " and Group4Code=" & Group4Flag
            Else
                WhereClause += " Group4Code=" & Group4Flag
            End If
        End If
        If Group5Flag <> 0 Then
            If WhereClause <> "" Then
                WhereClause += " and Group5Code=" & Group5Flag
            Else
                WhereClause += " Group5Code=" & Group5Flag
            End If
        End If

        If InClause.Trim <> "" Then
            If WhereClause <> "" Then
                strsql = "Select distinct(FirstName+' '+SecondName+' '+FamilyName) CusName,Email,NationalityName " & _
                        "from PcpMainMember a,Nationality b,InvHead c,InvData d,Product e " & _
                        "where a.NationalityID=b.NationalityID  " & _
                        "and ltrim(rtrim(a.PchCardNo))=left(c.InvLoyalCardNo,7) and c.InvID=d.InvID " & _
                        "and d.InvProdID=e.ProductID and " & WhereClause & _
                        " and isnull(Email,'')<>'' " & _
                        "and ltrim(rtrim(a.Status))='ACTIVE' and b.NationalityGroup in (" & InClause & ") "
            Else
                strsql = "Select distinct(FirstName+' '+SecondName+' '+FamilyName) CusName,Email,NationalityName " & _
                        "from PcpMainMember a,Nationality b " & _
                        "where a.NationalityID=b.NationalityID  " & _
                        " and isnull(Email,'')<>'' " & _
                        "and ltrim(rtrim(a.Status))='ACTIVE' and b.NationalityGroup in (" & InClause & ") "
            End If
        Else
            If WhereClause <> "" Then
                strsql = "Select distinct(FirstName+' '+SecondName+' '+FamilyName) CusName,Email,NationalityName " & _
                        "from PcpMainMember a,Nationality b,InvHead c,InvData d,Product e " & _
                        "where a.NationalityID=b.NationalityID  " & _
                        "and ltrim(rtrim(a.PchCardNo))=left(c.InvLoyalCardNo,7) and c.InvID=d.InvID " & _
                        "and d.InvProdID=e.ProductID and " & WhereClause & _
                        " and isnull(Email,'')<>'' " & _
                        "and ltrim(rtrim(a.Status))='ACTIVE' "
            Else
                strsql = "Select distinct(FirstName+' '+SecondName+' '+FamilyName) CusName,Email,NationalityName " & _
                        "from PcpMainMember a,Nationality b " & _
                        "where a.NationalityID=b.NationalityID  " & _
                        " and isnull(Email,'')<>'' " & _
                        "and ltrim(rtrim(a.Status))='ACTIVE' "
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpMembersForTargetedEmail = ds
    End Function
    Public Function GetPcpSubMembers(ByVal NationalityFlag As Integer, ByVal Status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If NationalityFlag = 0 Then
            strsql = "select a.FirstName+' '+SecondName+' '+FamilyName MainName,a.HomePhone,b.NationalityName,c.GovernorateName,d.AreaName, " & _
                    "e.SchCardNo,e.PchCardNo,e.KidsName,e.School,e.Independent " & _
                    "from PcpMainMember a,Nationality b,Governorate c,Area d,PcpSubMember e " & _
                    "where ltrim(rtrim(a.PchCardNo))=ltrim(rtrim(e.PchCardNo)) and a.NationalityID=b.NationalityID and a.GovernorateID=c.GovernorateID and a.CityID=d.AreaID " & _
                    "and ltrim(rtrim(e.Status))='" & Status & "' order by e.PchCardNo,e.SchCardNo"
        Else
            strsql = "select a.FirstName+' '+SecondName+' '+FamilyName MainName,a.HomePhone,b.NationalityName,c.GovernorateName,d.AreaName, " & _
                    "e.SchCardNo,e.PchCardNo,e.KidsName,e.School,e.Independent " & _
                    "from PcpMainMember a,Nationality b,Governorate c,Area d,PcpSubMember e " & _
                    "where ltrim(rtrim(a.PchCardNo))=ltrim(rtrim(e.PchCardNo)) and a.NationalityID=b.NationalityID and a.GovernorateID=c.GovernorateID and a.CityID=d.AreaID " & _
                    "and ltrim(rtrim(e.Status))='" & Status & "' and b.NationalityGroup=" & NationalityFlag & " order by e.PchCardNo,e.SchCardNo"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpSubMembers = ds
    End Function
    Public Function GetEventsMembers(ByVal KuwaitiFlag As Integer, ByVal ArabFlag As Integer, ByVal EuroFlag As Integer, ByVal AsianFlag As Integer, ByVal OutFlag As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim Flags As String
        If KuwaitiFlag = 1 Then
            Flags = "1,"
        Else
            Flags = "1"
        End If
        If ArabFlag = 1 Then
            Flags += "2,"
        End If
        If EuroFlag = 1 Then
            Flags += "3,"
        End If
        If AsianFlag = 1 Then
            Flags += "4"
        End If
        If Right(Flags, 1) = "," Then
            Flags = Flags.Replace(",", " ")
            Flags = Flags.Trim.Replace(" ", ",")
        End If
        strsql = "select a.FirstName+' '+SecondName+' '+FamilyName MainName, " & _
                "e.SchCardNo,e.PchCardNo,e.KidsName,e.School,e.Independent " & _
                "from PcpMainMember a,Nationality b " & _
                "where a.NationalityID=b.NationalityID " & _
                "order by MainName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEventsMembers = ds
    End Function
    Public Function GetPcpSubMembersByMainMember(ByVal CardNo As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select SchCardNo from PcpSubMember " & _
                "where ltrim(rtrim(PchCardNo))='" & Trim(CardNo) & "' " & _
                "and ltrim(rtrim(Status))='ACTIVE' and Independent=0 order by SchCardNo"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpSubMembersByMainMember = ds
    End Function
    Public Function GetPcpMainMemberData(ByVal CardNo As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* " & _
                "from PcpMainMember a " & _
                "where ltrim(rtrim(PchCardNo))='" & Trim(CardNo) & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpMainMemberData = ds
    End Function
    Public Function GetPcpSubMemberData(ByVal CardNo As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* " & _
                "from PcpSubMember a " & _
                "where ltrim(rtrim(SchCardNo))='" & Trim(CardNo) & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpSubMemberData = ds
    End Function
    Public Function GetPcpMobileData(ByVal CardNo As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* " & _
                "from PcpMobilePhone a " & _
                "where ltrim(rtrim(CardNo))='" & Trim(CardNo) & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPcpMobileData = ds
    End Function
    Public Function IsPchExist(ByVal PchCardNumber As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select isnull(count(*),0) from PcpMainMember " & _
        " where ltrim(rtrim(PchCardNo))='" & Trim(PchCardNumber) & "'"
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ngroup As Integer = 0
        Try
            ngroup = CInt(sqlCmd.ExecuteScalar)
        Catch ex As Exception
            ngroup = 0
        End Try
        sqlCon.Close()
        If ngroup = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    Public Function GetLastSch(ByVal PchCardNumber As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select isnull(max(SchCardNo),0) LastNum from PcpSubMember " & _
        " where ltrim(rtrim(PchCardNo))='" & Trim(PchCardNumber) & "'"
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ngroup As Integer = 0
        Dim LastNum As String
        Try
            LastNum = sqlCmd.ExecuteScalar
            ngroup = Right(LastNum, 2)
        Catch ex As Exception
            ngroup = 0
        End Try
        sqlCon.Close()
        Return ngroup
    End Function
    Public Function AddPcpMember(ByVal ParentCard As String, ByVal FirstName As String, ByVal SecondName As String, ByVal FamilyName As String, ByVal Sex As Integer, _
                ByVal DOB As String, ByVal CID As String, ByVal Marital As Integer, ByVal NationID As Integer, ByVal Area As String, ByVal Block As String, ByVal Street As String, _
                ByVal Avenue As String, ByVal Bldg As String, ByVal Floor As String, ByVal Flat As String, ByVal AdrOther As String, ByVal GovernorateID As Integer, _
                ByVal CityID As Integer, ByVal PB As String, ByVal PostArea As String, ByVal Zip As String, ByVal Hphone As String, ByVal Wphone As String, ByVal Mphone As String, _
                ByVal Extn As String, ByVal Fax As String, ByVal Email As String, ByVal HomeFlag As Integer, ByVal OfficeFlag As Integer, ByVal MobileFlag As Integer, ByVal EmailFlag As Integer, ByVal FaxFlag As Integer, _
                ByVal Child1 As String, ByVal Child2 As String, ByVal Child3 As String, ByVal Child4 As String, ByVal Child5 As String, ByVal Boy1 As Integer, ByVal Boy2 As Integer, ByVal Boy3 As Integer, ByVal Boy4 As Integer, _
                ByVal Boy5 As Integer, ByVal Child1Dob As String, ByVal Child2Dob As String, ByVal Child3Dob As String, ByVal Child4Dob As String, ByVal Child5Dob As String, ByVal Child1School As String, _
                ByVal Child2School As String, ByVal Child3School As String, ByVal Child4School As String, ByVal Child5School As String, _
                ByVal Child1Card As String, ByVal Child2Card As String, ByVal Child3Card As String, ByVal Child4Card As String, ByVal Child5Card As String, ByVal RelationID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlTrans As SqlTransaction
        sqlCon.Open()
        '
        sqlTrans = sqlCon.BeginTransaction
        '
        Dim CardAndNames As String = "<br><br><table width=60% border=1><tr><td bgcolor=AliceBlue colspan=2 align=center><b>Membership Details</b></td></tr>"
        CardAndNames = CardAndNames + "<tr bgcolor=AliceBlue><td width=60%>Member Name</td><td width=40%>Card No.</td></tr>"
        CardAndNames = CardAndNames + "<tr><td>" + FirstName + " " + SecondName + " " + FamilyName + "</td><td>" + ParentCard + "</td></tr>"
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCmd.Transaction = sqlTrans
        strsql = "Insert into PcpMainMember(PchCardNo,FirstName,SecondName,FamilyName,Gender,DateOfBirth,CivilIDNo, " & _
            "MaritalStatus,NationalityID,Area,Block,Street,Avenue,BldgNo,FloorNo,FlatNo,OtherDetails,GovernorateID," & _
            "CityID,PoBoxNo,PostalArea,ZipCode,HomePhone,WorkPhone,WorkExtn,Fax,Email,ContactHome," & _
            "ContactOffice,ContactMobile,ContactEmail,ContactFax,MOBILE) " & _
            "Values('" & ParentCard & "','" & FirstName & "','" & SecondName & "','" & FamilyName & "'," & Sex & ",'" & _
            ValidDate(DOB) & "','" & CID & "'," & Marital & "," & NationID & ",'" & Area & "','" & Block & "','" & _
            Street & "','" & Avenue & "','" & Bldg & "','" & Floor & "','" & Flat & "','" & AdrOther & "'," & _
            GovernorateID & "," & CityID & ",'" & PB & "','" & PostArea & "','" & Zip & "','" & Hphone & "','" & Wphone & "','" & _
            Extn & "','" & Fax & "','" & Email & "'," & HomeFlag & "," & OfficeFlag & "," & MobileFlag & "," & EmailFlag & "," & FaxFlag & ",'" & Mphone & "')"
        sqlCmd.CommandText = strsql
        Try
            sqlCmd.ExecuteNonQuery()
            If Mphone.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpMobilePhone (CardNo,PhoneNo) " & _
                    "values('" & ParentCard & "','" & Mphone & "')"
                sqlCmd.ExecuteNonQuery()
            End If
            If Child1.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child1Card & "','" & Child1 & "'," & Boy1 & ",'" & ValidDate(Child1Dob) & "','" & Child1School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
                CardAndNames = CardAndNames + "<tr bgcolor=AliceBlue><td>" + Child1 + "</td><td>" + Child1Card + "</td></tr>"
            End If
            If Child2.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child2Card & "','" & Child2 & "'," & Boy2 & ",'" & ValidDate(Child2Dob) & "','" & Child2School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
                CardAndNames = CardAndNames + "<tr><td>" + Child2 + "</td><td>" + Child2Card + "</td></tr>"
            End If
            If Child3.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child3Card & "','" & Child3 & "'," & Boy3 & ",'" & ValidDate(Child3Dob) & "','" & Child3School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
                CardAndNames = CardAndNames + "<tr bgcolor=AliceBlue><td>" + Child3 + "</td><td>" + Child3Card + "</td></tr>"
            End If
            If Child4.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child4Card & "','" & Child4 & "'," & Boy4 & ",'" & ValidDate(Child4Dob) & "','" & Child4School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
                CardAndNames = CardAndNames + "<tr><td>" + Child4 + "</td><td>" + Child4Card + "</td></tr>"
            End If
            If Child5.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child5Card & "','" & Child5 & "'," & Boy5 & ",'" & ValidDate(Child5Dob) & "','" & Child5School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
                CardAndNames = CardAndNames + "<tr bgcolor=AliceBlue><td>" + Child5 + "</td><td>" + Child5Card + "</td></tr>"
            End If
            CardAndNames = CardAndNames + "</table>"
            If Email.Trim <> "" Then
                Dim dsEmail As DataSet
                Dim IcData As New ICdataLogic
                dsEmail = IcData.GetPcpEmailData(1)
                Dim emRow As DataRow
                Dim MailSubject As String = ""
                Dim MailText As String = ""
                Dim MailTypeID As Integer = 0
                For Each emRow In dsEmail.Tables(0).Rows
                    MailSubject = emRow("MailSubject")
                    MailText = emRow("MailText")
                    MailTypeID = emRow("MailID")
                Next
                MailText = MailText + CardAndNames
                If MailText.Trim <> "" Then
                    MailText = "Dear " + FirstName + " " + SecondName + " " + FamilyName + ",<br><br>" + MailText
                    strsql = "Insert into PcpEmailHistory (MailTypeID,MailSubject,MailText,MailToAddress) " & _
                        "values(" & MailTypeID & ",'" & MailSubject & "','" & MailText & "','" & Email & "')"
                    sqlCmd.CommandText = strsql
                    sqlCmd.ExecuteNonQuery()
                End If
            End If
            sqlTrans.Commit()
        Catch ex As Exception
            sqlTrans.Rollback()
            Return "ERROR"
        End Try
        Return "OK"
    End Function
    Public Function AddPcpSubMember(ByVal ParentCard As String, _
            ByVal Child1 As String, ByVal Child2 As String, ByVal Child3 As String, ByVal Child4 As String, ByVal Child5 As String, ByVal Boy1 As Integer, ByVal Boy2 As Integer, ByVal Boy3 As Integer, ByVal Boy4 As Integer, _
            ByVal Boy5 As Integer, ByVal Child1Dob As String, ByVal Child2Dob As String, ByVal Child3Dob As String, ByVal Child4Dob As String, ByVal Child5Dob As String, ByVal Child1School As String, _
            ByVal Child2School As String, ByVal Child3School As String, ByVal Child4School As String, ByVal Child5School As String, _
            ByVal Child1Card As String, ByVal Child2Card As String, ByVal Child3Card As String, ByVal Child4Card As String, ByVal Child5Card As String, ByVal RelationID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlTrans As SqlTransaction
        sqlCon.Open()
        '
        sqlTrans = sqlCon.BeginTransaction
        '
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCmd.Transaction = sqlTrans
        Try
            If Child1.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child1Card & "','" & Child1 & "'," & Boy1 & ",'" & ValidDate(Child1Dob) & "','" & Child1School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
            End If
            If Child2.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child2Card & "','" & Child2 & "'," & Boy2 & ",'" & ValidDate(Child2Dob) & "','" & Child2School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
            End If
            If Child3.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child3Card & "','" & Child3 & "'," & Boy3 & ",'" & ValidDate(Child3Dob) & "','" & Child3School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
            End If
            If Child4.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child4Card & "','" & Child4 & "'," & Boy4 & ",'" & ValidDate(Child4Dob) & "','" & Child4School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
            End If
            If Child5.Trim <> "" Then
                sqlCmd.CommandText = "insert into PcpSubMember (PchCardNo,SchCardNo,KidsName,Gender,DateOfBirth,School,Relationship) " & _
                    "values('" & ParentCard & "','" & Child5Card & "','" & Child5 & "'," & Boy5 & ",'" & ValidDate(Child5Dob) & "','" & Child5School & "'," & RelationID & ")"
                sqlCmd.ExecuteNonQuery()
            End If
            sqlTrans.Commit()
        Catch ex As Exception
            sqlTrans.Rollback()
            Return "ERROR"
        End Try
        Return "OK"
    End Function
    Public Function AddPcpNonMember(ByVal CusName As String, ByVal Email As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlTrans As SqlTransaction
        sqlCon.Open()
        '
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        Try
            sqlCmd.CommandText = "insert into PcpNonMember (CustomerName,EmailAddress) " & _
                "values('" & CusName & "','" & Email & "')"
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            Return "ERROR"
        End Try
        Return "OK"
    End Function
    Public Function UpdatePcpSubMember(ByVal ChildCard As String, _
        ByVal Child1 As String, ByVal Boy1 As Integer, ByVal Child1Dob As String, ByVal Child1School As String, _
        ByVal RelationID As Integer, ByVal Independent As Integer, ByVal Status As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlTrans As SqlTransaction
        sqlCon.Open()
        '
        sqlTrans = sqlCon.BeginTransaction
        '
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCmd.Transaction = sqlTrans
        Try
            If Child1.Trim <> "" Then
                sqlCmd.CommandText = "update PcpSubMember set KidsName='" & Child1 & "',Gender=" & Boy1 & "," & _
                    "DateOfBirth='" & ValidDate(Child1Dob) & "',School='" & Child1School & "',Status='" & Status & "',Independent=" & Independent & _
                    " where ltrim(rtrim(SchCardNo))='" & Trim(ChildCard) & "'"
                sqlCmd.ExecuteNonQuery()
            End If
            sqlTrans.Commit()
        Catch ex As Exception
            sqlTrans.Rollback()
            Return "ERROR"
        End Try
        Return "OK"
    End Function
    Public Function UpdatePcpMember(ByVal ParentCard As String, ByVal FirstName As String, ByVal SecondName As String, ByVal FamilyName As String, ByVal Sex As Integer, _
            ByVal DOB As String, ByVal CID As String, ByVal Marital As Integer, ByVal NationID As Integer, ByVal Area As String, ByVal Block As String, ByVal Street As String, _
            ByVal Avenue As String, ByVal Bldg As String, ByVal Floor As String, ByVal Flat As String, ByVal AdrOther As String, ByVal GovernorateID As Integer, _
            ByVal CityID As Integer, ByVal PB As String, ByVal PostArea As String, ByVal Zip As String, ByVal Hphone As String, ByVal Wphone As String, ByVal Mphone As String, _
            ByVal Extn As String, ByVal Fax As String, ByVal Email As String, ByVal HomeFlag As Integer, ByVal OfficeFlag As Integer, ByVal MobileFlag As Integer, ByVal EmailFlag As Integer, ByVal FaxFlag As Integer, ByVal Status As Integer) As String

        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlTrans As SqlTransaction
        sqlCon.Open()
        '
        sqlTrans = sqlCon.BeginTransaction
        '
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCmd.Transaction = sqlTrans
        Dim mStatus As String
        If Status = 0 Then
            mStatus = "INACTIVE"
        Else
            mStatus = "ACTIVE"
        End If
        strsql = "Update PcpMainMember set FirstName='" & FirstName & "',SecondName='" & SecondName & "'," & _
            "FamilyName='" & FamilyName & "',Gender=" & Sex & ",DateOfBirth='" & ValidDate(DOB) & "'," & _
            "CivilIDno='" & CID & "',MaritalStatus=" & Marital & ",NationalityID=" & NationID & "," & _
            "Area='" & Area & "',Block='" & Block & "',Street='" & Street & "',Avenue='" & Avenue & "'," & _
            "BldgNo='" & Bldg & "',FloorNo='" & Floor & "',FlatNo='" & Flat & "',OtherDetails='" & AdrOther & "'," & _
            "GovernorateID=" & GovernorateID & ",CityID=" & CityID & ",PoBoxNo='" & PB & "',PostalArea='" & PostArea & "'," & _
            "ZipCode='" & Zip & "',HomePhone='" & Hphone & "',WorkPhone='" & Wphone & "',WorkExtn='" & Extn & "'," & _
            "Fax='" & Fax & "',Email='" & Email & "',ContactHome=" & HomeFlag & ",ContactOffice=" & OfficeFlag & "," & _
            "ContactMobile=" & MobileFlag & ",ContactEmail=" & EmailFlag & ",ContactFax=" & FaxFlag & ",Status='" & mStatus & "',MOBILE='" & Mphone & "',Trfrd='N' " & _
            "where ltrim(rtrim(PchCardNo))='" & Trim(ParentCard) & "'"
        sqlCmd.CommandText = strsql
        Try
            sqlCmd.ExecuteNonQuery()
            If Mphone.Trim <> "" Then
                sqlCmd.CommandText = "delete from PcpMobilePhone where ltrim(rtrim(CardNo))='" & Trim(ParentCard) & "'"
                sqlCmd.ExecuteNonQuery()
                sqlCmd.CommandText = "insert into PcpMobilePhone (CardNo,PhoneNo) " & _
                    "values('" & ParentCard & "','" & Trim(Mphone) & "')"
                sqlCmd.ExecuteNonQuery()
            End If
            sqlCmd.CommandText = "update PcpSubMember set Status='" & mStatus & "' " & _
                "where ltrim(rtrim(PchCardNo))='" & Trim(ParentCard) & "' and Independent=0"
            sqlCmd.ExecuteNonQuery()
            sqlTrans.Commit()
        Catch ex As Exception
            sqlTrans.Rollback()
            Return "ERROR"
        End Try
        Return "OK"
    End Function
    Public Function GetEmployeesByCompany(ByVal CompanyCode As Integer, ByVal PostID As Integer, ByVal NationID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim CompFilter, PostFilter, NationFilter As String
        If CompanyCode > 0 Then
            CompFilter = " and EmpCompanyID=" & CompanyCode
        End If
        If PostID > 0 Then
            PostFilter = " and EmpDesignationID=" & PostID
        End If
        If NationID > 0 Then
            NationFilter = " and EmpNationalityID=" & NationID
        End If
        sqlCon.Open()
        strsql = "select EmpID,EmpPfNo,EmpName,EmpPresentPhone,EmpEmail,EmpAppointmentDate,DesignationName,NationalityName  " & _
                "from Employee a,EmployeeDesignation b,Nationality c " & _
                "where(a.EmpDesignationID = b.DesignationID And a.EmpNationalityID = c.NationalityID) " & _
                CompFilter & " " & PostFilter & " " & NationFilter & " order by EmpName"
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeesByCompany = ds
    End Function
    Public Function GetEmployeesBenefitByCompany(ByVal CompanyCode As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim CompFilter, PostFilter, NationFilter As String
        If CompanyCode > 0 Then
            CompFilter = " and EmpCompanyID=" & CompanyCode
        End If
        sqlCon.Open()
        strsql = "select a.EmpID,EmpPfNo,EmpName,BasicSalary,Allowance,HouseRent,LoanAmount,LoanDeduction  " & _
                "from Employee a,EmployeeBenefit b " & _
                "where a.EmpID=b.EmpID " & CompFilter & " order by EmpName"
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeesBenefitByCompany = ds
    End Function
    Public Function GetAssetsByCompanyEDT(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal LocationID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If LocationID = 0 Then
            If GroupID = 0 Then
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & " " & _
                        " and a.PurchaseAmount = 0 order by a.AssetNumber"
            Else
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & "  " & _
                        "and a.GroupID=" & GroupID & " " & _
                        " and a.PurchaseAmount = 0 order by a.AssetNumber"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select a.* " & _
                        "from Asset a " & _
                        "where a.CompanyID=" & CompanyID & " " & _
                        "and a.DptID=" & LocationID & "  and a.PurchaseAmount = 0 order by a.AssetName"
            Else
                strsql = "select a.* " & _
                        "from Asset a " & _
                        "where a.CompanyID=" & CompanyID & "  " & _
                        "and a.GroupID=" & GroupID & " " & _
                        "and a.DptID=" & LocationID & "  and a.PurchaseAmount = 0 order by a.AssetName"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetsByCompanyEDT = ds
    End Function
    Public Function GetAssetsByCompany(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal LocationID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        'Dim dateFrom As Date
        'Try
        '    dateFrom = Date.Parse(DFROM)
        'Catch ex As Exception
        '    Err.Raise(666, "GetStockStatusReport", "Invalid Date")
        '    Exit Function
        'End Try
        'Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        'sFdate = sFdate + " 00:00"

        'Dim dateTo As Date
        'Try
        '    dateTo = Date.Parse(DTO)
        'Catch ex As Exception
        '    Err.Raise(666, "GetStockStatusReport", "Invalid Date")
        '    Exit Function
        'End Try
        'Dim sTdate As String = dateTo.ToString("MM/dd/yyyy")
        'sTdate = sTdate + " 23:59"

        Dim Drange As String = "  "

        If LocationID = 0 Then
            If GroupID = 0 Then
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & " " & Drange & _
                        "order by a.AssetNumber"
            Else
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & "  " & Drange & _
                        "and a.GroupID=" & GroupID & " " & _
                        "order by a.AssetNumber"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select a.* " & _
                        "from Asset a " & _
                        "where a.CompanyID=" & CompanyID & " " & _
                        "and a.DptID=" & LocationID & Drange & " order by a.AssetName"
            Else
                strsql = "select a.* " & _
                        "from Asset a " & _
                        "where a.CompanyID=" & CompanyID & "  " & _
                        "and a.GroupID=" & GroupID & " " & _
                        "and a.DptID=" & LocationID & Drange & " order by a.AssetName"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetsByCompany = ds
    End Function
    Public Function GetAssetsSummary(ByVal DFROM As String, ByVal DTO As String, ByVal status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        Dim statuscode As String

        If status = "NO FILTER" Then
            statuscode = " "
        Else
            statuscode = " and assetstatus = '" & status & "'"
        End If

        Dim dateFrom As Date
        Try
            dateFrom = Date.Parse(DFROM)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        sFdate = sFdate + " 00:00"

        Dim dateTo As Date
        Try
            dateTo = Date.Parse(DTO)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sTdate As String = dateTo.ToString("MM/dd/yyyy")
        sTdate = sTdate + " 23:59"

        Dim Drange As String = " where PurchaseDate between '" & sFdate & "' and '" & sTdate & "' "


        strsql = "select GroupID,Sum(PurchaseAmount)PA,Sum(CurrentValue) BV from Asset " & Drange & statuscode & " group by GroupID"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetsSummary = ds
    End Function
    Public Function GetAssetsTrfrReport(ByVal GroupID As Integer, ByVal DFROM As String, ByVal DTO As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        Dim grp As String

        If GroupID = 0 Then
            grp = ""
        Else
            grp = " and A.GroupID = " & GroupID
        End If


        Dim dateFrom As Date
        Try
            dateFrom = Date.Parse(DFROM)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        sFdate = sFdate + " 00:00"

        Dim dateTo As Date
        Try
            dateTo = Date.Parse(DTO)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sTdate As String = dateTo.ToString("MM/dd/yyyy")
        sTdate = sTdate + " 23:59"


        'strsql = "select AST.AssetNumber,AST.AssetName,convert(char(10),A.SaleDate,103) SD,AST.PurchaseAmount, " & _
        '         "A.BookValue BookValue,A.SaleAmount,(A.SaleAmount-A.BookValue) PL,AST.LifeInMonths, " & _
        '         "DateDiff(month, AST.PurchaseDate,A.SaleDate) as LifeUsed,AST.AssetBrand Manufacturer, " & _
        '         "AST.Voucher,A.CustomerName as SoldTo,A.VoucherNo as ReceiptNo,A.Remarks  " & _
        '         "from AssetSale A,Asset AST where AST.ASSETID = A.ASSETID and A.SaleDate between '" & sFdate & "' and '" & sTdate & "'" & loc & grp

        strsql = "select A.AssetID,A.AssetNumber,A.AssetName,convert(char(10),H.TransferDate,103) TD,A.PurchaseAmount as AssetsValue,A.CurrentValue BookVal," & _
                 "A.LifeInMonths,(A.LifeInMonths-DateDiff(month,  A.PurchaseDate,H.TransferDate)) as Liferemain,A.AssetBrand Manufacturer,A.Voucher,H.TrfrRef as Reference,b1.AssetLocationName FLoc,b2.AssetLocationName TLoc,H.Remarks " & _
                 "from Asset A,AssetTransferHistory H,AssetLocation b1,AssetLocation b2 where A.AssetID = H.AssetID and " & _
                 "b1.AssetLocationID = H.FromLocationID and b2.AssetLocationID = H.ToLocationID and H.TransferDate between '" & sFdate & "' and '" & sTdate & "' " & grp
        'b.AssetLocationName()H.FromLocationID,H.ToLocationID
        ' where AP.AssetID = A.AssetID and AP.PurchDate between '" & sFdate & _
        '         "' and '" & sTdate & "' and  AP.LocationID = b.AssetLocationID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try

        'ds.Tables("ASSET").Columns.Add("PLA")
        'Dim drow As DataRow
        'For Each drow In ds.Tables("ASSET").Rows
        '    If drow("PL") < 0 Then
        '        drow("PLA") = "(" & (CDbl(drow("PL")) * -1).ToString("#0.000") & ")"
        '    Else
        '        drow("PLA") = drow("PL")
        '    End If
        'Next

        'ds.Tables("ASSET").Columns.Add(drow)
        sqlCon.Close()
        GetAssetsTrfrReport = ds

    End Function
    Public Function GetAssetsSalesReport(ByVal GroupID As Integer, ByVal LocationID As Integer, ByVal DFROM As String, ByVal DTO As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim loc As String
        Dim grp As String

        If GroupID = 0 Then
            grp = ""
        Else
            grp = " and AST.GroupID = " & GroupID
        End If

        If LocationID = 0 Then
            loc = ""
        Else
            loc = " and AST.DeptID = " & LocationID
        End If
        Dim dateFrom As Date
        Try
            dateFrom = Date.Parse(DFROM)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        sFdate = sFdate + " 00:00"

        Dim dateTo As Date
        Try
            dateTo = Date.Parse(DTO)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sTdate As String = dateTo.ToString("MM/dd/yyyy")
        sTdate = sTdate + " 23:59"


        strsql = "select AST.AssetNumber,AST.AssetName,convert(char(10),A.SaleDate,103) SD,AST.PurchaseAmount, " & _
                 "A.BookValue BookValue,A.SaleAmount,(A.SaleAmount-A.BookValue) PL,AST.LifeInMonths, " & _
                 "DateDiff(month, AST.PurchaseDate,A.SaleDate) as LifeUsed,AST.AssetBrand Manufacturer, " & _
                 "AST.Voucher,A.CustomerName as SoldTo,A.VoucherNo as ReceiptNo,A.Remarks  " & _
                 "from AssetSale A,Asset AST where AST.ASSETID = A.ASSETID and A.SaleDate between '" & sFdate & "' and '" & sTdate & "'" & loc & grp


        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try

        ds.Tables("ASSET").Columns.Add("PLA")
        Dim drow As DataRow
        For Each drow In ds.Tables("ASSET").Rows
            If drow("PL") < 0 Then
                drow("PLA") = "(" & (CDbl(drow("PL")) * -1).ToString("#0.000") & ")"
            Else
                drow("PLA") = drow("PL")
            End If
        Next

        'ds.Tables("ASSET").Columns.Add(drow)
        sqlCon.Close()
        GetAssetsSalesReport = ds

    End Function
    Public Function GetAssetsPurchaseReport(ByVal GroupID As Integer, ByVal LocationID As Integer, ByVal DFROM As String, ByVal DTO As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim loc As String
        Dim grp As String

        If GroupID = 0 Then
            grp = ""
        Else
            grp = " and A.GroupID = " & GroupID
        End If

        If LocationID = 0 Then
            loc = ""
        Else
            loc = " and AP.LocationID = " & LocationID
        End If
        Dim dateFrom As Date
        Try
            dateFrom = Date.Parse(DFROM)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        sFdate = sFdate + " 00:00"

        Dim dateTo As Date
        Try
            dateTo = Date.Parse(DTO)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sTdate As String = dateTo.ToString("MM/dd/yyyy")
        sTdate = sTdate + " 23:59"
        '"select a.*,convert(char(10),a.PurchaseDate,103) PD,b.AssetLocationName " & _
        '               "from Asset a,AssetLocation b " & _
        '               "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & " " & Drange & statuscode & _
        '               "order by a.AssetNumber"
        strsql = "select AP.AssetID,A.AssetNumber,A.AssetName,convert(char(10),AP.PurchDate,103) PD,AP.InvoiceAmount AssetValue," & _
                 "A.Voucher,A.SupplierName,A.LifeInMonths,A.AssetBrand Manufacturer,b.AssetLocationName,AP.Remarks " & _
                 "from AssetPurchase AP, ASSET A,AssetLocation b where AP.AssetID = A.AssetID and AP.PurchDate between '" & sFdate & _
                 "' and '" & sTdate & "' and  AP.LocationID = b.AssetLocationID " & loc & grp

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetsPurchaseReport = ds

    End Function
    Public Function GetAssetsByCompanyDR(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal LocationID As Integer, ByVal DFROM As String, ByVal DTO As String, ByVal status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        Dim statuscode As String

        If status = "NO FILTER" Then
            statuscode = " "
        Else
            statuscode = " and a.assetstatus = '" & status & "'"
        End If

        Dim dateFrom As Date
        Try
            dateFrom = Date.Parse(DFROM)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        sFdate = sFdate + " 00:00"

        Dim dateTo As Date
        Try
            dateTo = Date.Parse(DTO)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sTdate As String = dateTo.ToString("MM/dd/yyyy")
        sTdate = sTdate + " 23:59"

        Dim Drange As String = " and a.PurchaseDate between '" & sFdate & "' and '" & sTdate & "' "

        If LocationID = 0 Then
            If GroupID = 0 Then
                strsql = "select a.*,convert(char(10),a.PurchaseDate,103) PD,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & " " & Drange & statuscode & _
                        "order by a.AssetNumber"
            Else
                strsql = "select a.*,convert(char(10),a.PurchaseDate,103) PD,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & "  " & Drange & statuscode & _
                        "and a.GroupID=" & GroupID & " " & _
                        "order by a.AssetNumber"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select a.*,convert(char(10),a.PurchaseDate,103) PD,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & " " & _
                        "and a.DptID=" & LocationID & Drange & statuscode & " order by a.AssetName"
            Else
                strsql = "select a.*,convert(char(10),a.PurchaseDate,103) PD,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & "  " & _
                        "and a.GroupID=" & GroupID & " " & _
                        "and a.DptID=" & LocationID & Drange & statuscode & " order by a.AssetName"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetsByCompanyDR = ds
    End Function
    Public Function GetAssetsByCompanyName(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal LocationID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If LocationID = 0 Then
            If GroupID = 0 Then
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & " " & _
                        "order by a.AssetName"
            Else
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where a.DptID=b.AssetLocationID and a.CompanyID=" & CompanyID & "  " & _
                        "and a.GroupID=" & GroupID & " " & _
                        "order by a.AssetName"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select a.* " & _
                        "from Asset a " & _
                        "where a.CompanyID=" & CompanyID & " " & _
                        "and a.DptID=" & LocationID & " order by a.AssetName"
            Else
                strsql = "select a.* " & _
                        "from Asset a " & _
                        "where a.CompanyID=" & CompanyID & "  " & _
                        "and a.GroupID=" & GroupID & " " & _
                        "and a.DptID=" & LocationID & " order by a.AssetName"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetsByCompanyName = ds
    End Function
    Public Function GetCompanyWarehouses(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Warehouse " & _
                "where CompanyID=" & CompanyID & _
                " order by WarehouseName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("WAREHOUSE")
        Try
            da.Fill(ds, "WAREHOUSE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyWarehouses = ds
    End Function
    Public Function GetCustomerDetails(ByVal CustomerID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* from Customer a " & _
                "where a.CusID=" & CustomerID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomerDetails = ds
    End Function
    Public Function EmployeePhotoExist(ByVal EmpID As Integer) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select count(*) from EmployeePhoto where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim mcount As Integer
        mcount = sqlCmd.ExecuteScalar
        If mcount = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    Public Function GetEmployeePhoto(ByVal EmpID As Integer) As Byte()
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select EmpPhoto from EmployeePhoto where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim barray As Byte()
        Dim fpath As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            If Not IsDBNull(rdr("EmpPhoto")) Then
                barray = rdr("EmpPhoto")
            End If
        End While
        Return barray
    End Function
    Public Function GetEmployeeDetails(ByVal EmpID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* from Employee a " & _
                "where a.EmpID=" & EmpID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeeDetails = ds
    End Function
    Public Function GetCustomerID(ByVal CusCode As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.CusID from Customer a " & _
                "where a.CusCode='" & CusCode & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim CusID As Integer
        Try
            CusID = sqlCmd.ExecuteScalar()
        Catch ex As SqlException

        End Try
        sqlCon.Close()
        GetCustomerID = CusID
    End Function
    Public Function CheckCompany(ByVal CompanyName As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) from Company a " & _
                "where a.CompanyName='" & CompanyName & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim CusID As Integer = 0
        Try
            CusID = sqlCmd.ExecuteScalar()
        Catch ex As SqlException
            CusID = 1
        End Try
        sqlCon.Close()
        CheckCompany = CusID
    End Function
    Public Function CheckAssetPurchased(ByVal AssetID As Integer) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) from AssetPurchase a " & _
                "where a.AssetID=" & AssetID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim CusID As Integer = 0
        Try
            CusID = sqlCmd.ExecuteScalar()
        Catch ex As SqlException
            CusID = 1
        End Try
        sqlCon.Close()
        CheckAssetPurchased = CusID
    End Function
    Public Function CheckAssetSold(ByVal AssetID As Integer) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) from AssetSale a " & _
                "where a.AssetID=" & AssetID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim CusID As Integer = 0
        Try
            CusID = sqlCmd.ExecuteScalar()
        Catch ex As SqlException
            CusID = 1
        End Try
        sqlCon.Close()
        CheckAssetSold = CusID
    End Function
    Public Function GetCustomerCode(ByVal CusID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.CusCode from Customer a " & _
                "where a.CusID='" & CusID & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim CusCode As String
        Try
            CusCode = sqlCmd.ExecuteScalar()
        Catch ex As SqlException

        End Try
        sqlCon.Close()
        GetCustomerCode = CusCode
    End Function

    Public Function GetAssetDetails(ByVal AssetID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* from Asset a " & _
                "where a.AssetID=" & AssetID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetDetails = ds
    End Function
    Public Function GetAssetDetailsFromCode(ByVal AssetCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* from Asset a " & _
                "where ltrim(rtrim(a.AssetNumber))='" & AssetCode.Trim & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetDetailsFromCode = ds
    End Function
    Public Function GetAssetAccounts(ByVal AssetID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.AccountNumber Fam,a.AccountCenter Fas," & _
                "c.AccountNumber Adm,c.AccountCenter Ads,d.AccountNumber Exm,d.AccountCenter Exs, " & _
                "e.AccountNumber Pcm,e.AccountCenter Pcs " & _
                "from Account a with (NoLock),Asset b with (NoLock),Account c with (NoLock)," & _
                "Account d with (NoLock),Account e with (NoLock) " & _
                "where a.AccountID=b.FaAccountID and c.AccountID=b.AdAccountID " & _
                "and d.AccountID=b.DpAccountID and e.AccountID=b.PcAccountID " & _
                " and AssetID=" & AssetID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetAccounts = ds
    End Function
    Public Function GetAssetAccountsSale(ByVal AssetID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.AccountNumber Fam,a.AccountCenter Fas," & _
                "c.AccountNumber Adm,c.AccountCenter Ads,d.AccountNumber Exm,d.AccountCenter Exs, " & _
                "e.AccountNumber Pcm,e.AccountCenter Pcs " & _
                "from Account a with (NoLock),Asset b with (NoLock),Account c with (NoLock)," & _
                "Account d with (NoLock),Account e with (NoLock) " & _
                "where a.AccountID=b.FaAccountID and c.AccountID=b.AdAccountID " & _
                "and d.AccountID=b.DpAccountID and e.AccountID=b.PcAccountID " & _
                " and AssetID=" & AssetID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetAccountsSale = ds
    End Function

    Public Function GetCurrencies() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CurrencyID,CurrencyCode,CurrencyName from Currency " & _
                "order by CurrencyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CURRENCY")
        Try
            da.Fill(ds, "CURRENCY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCurrencies = ds
    End Function
    Public Function GetShowroomsRolPrompt(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.DepartmentID,a.DepartmentName," & _
                "0 MinimumQty " & _
                "from Department a " & _
                "where a.CompanyID=" & CompanyID & " and a.ShowroomFlag = 1 " & _
                "order by a.DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShowroomsRolPrompt = ds
    End Function
    Public Function GetSuppliersProdPrompt(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID,a.SupName," & _
                "0 ValidSup " & _
                "from Supplier a " & _
                "where a.CompanyID=" & CompanyID & _
                " order by a.SupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSuppliersProdPrompt = ds
    End Function
    Public Function GetBrandCode(ByVal BrandID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select GroupCode from ProductGroup where GroupID=" & BrandID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim BrandCode As String
        Try
            BrandCode = sqlCmd.ExecuteScalar
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBrandCode = BrandCode
    End Function

    Public Function GetAllGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GroupID,a.GroupCode,a.GroupName " & _
                "from CustomerGroup a  " & _
                " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllGroups = ds
    End Function
    Public Function GetAllDocTypes() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * " & _
                "from FasDoc a  " & _
                " order by Doc_Name"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllDocTypes = ds
    End Function
    Public Function GetAllAssetGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GroupID,a.GroupName " & _
                "from AssetGroup a  " & _
                " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllAssetGroups = ds
    End Function
    Public Function GetAllEvents() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from SaleEvents order by EventName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("EVENTS")
        Try
            da.Fill(ds, "EVENTS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllEvents = ds
    End Function
    Public Function GetAllPosRequest(ByVal ShowroomID As Integer, ByVal SalesmanID As Integer, ByVal Status As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If SalesmanID = 0 And ShowroomID = 0 Then
            If Status = 0 Then
                strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                        "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                        "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                        "and a.ReqSalesmanID=c.SalesmanID order by ReqDateTime desc"
            Else
                strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                        "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                        "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                        "and a.ReqSalesmanID=c.SalesmanID and ltrim(rtrim(ReqStatus))='PENDING' order by ReqDateTime desc"
            End If
        Else
            If SalesmanID > 0 Then
                If Status = 0 Then
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqSalesmanID=" & SalesmanID & " order by ReqDateTime desc"
                Else
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqSalesmanID=" & SalesmanID & " and ltrim(rtrim(ReqStatus))='PENDING' order by ReqDateTime desc"
                End If
            End If
            If ShowroomID > 0 Then
                If Status = 0 Then
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqShowroomID=" & ShowroomID & " order by ReqDateTime desc"
                Else
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqShowroomID=" & ShowroomID & " and ltrim(rtrim(ReqStatus))='PENDING' order by ReqDateTime desc"
                End If
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("REQUEST")
        Try
            da.Fill(ds, "REQUEST")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllPosRequest = ds
    End Function
    Public Function GetGroupLevels() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select LevelID,LevelName from GroupLevel " & _
                 "order by LevelName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("LEVEL")
        Try
            da.Fill(ds, "LEVEL")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("LevelID") = 0
        dRow("LevelName") = "ALL LEVELS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetGroupLevels = ds
    End Function
    Public Function GetSavedInvoiceNumbers(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If InvStatus = "SAVE" Then
            strsql = "select InvID,InvNO from InvHead " & _
                     "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & " and InvStatus='" & InvStatus & "'"
        Else
            strsql = "select InvID,InvNO from InvHead " & _
                     "where InvStatus='" & InvStatus & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSavedInvoiceNumbers = ds
    End Function
    Public Function GetSavedInvoiceHeader(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from InvHead " & _
                 "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & " and InvStatus='" & InvStatus & "' order by InvDateTime desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSavedInvoiceHeader = ds
    End Function
    Public Function GetSavedPurhRtnHeader(ByVal RtnID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseRtnHead " & _
                 "where PrID=" & RtnID & " order by prid desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSavedPurhRtnHeader = ds
    End Function
    Public Function GetWholesaleInvoiceHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from InvwHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by InvID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleInvoiceHeaderByStatus = ds
    End Function
    Public Function GetWholesaleReturnHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from RtnwHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by RtnID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleReturnHeaderByStatus = ds
    End Function

    Public Function GetWholesaleQuotationHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from QtnHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by QtnID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleQuotationHeaderByStatus = ds
    End Function
    Public Function GetWholesaleLpoHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from LpoHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by LpoID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleLpoHeaderByStatus = ds
    End Function
    Public Function GetReturnHeaderByStatus(ByVal CompanyID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseRtnHead " & _
                 "where PrCompanyID=" & CompanyID & " and  PrStatus='" & InvStatus & "' order by PrID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetReturnHeaderByStatus = ds
    End Function
    Public Function GetOrderHeader(ByVal CompanyID As Integer, ByVal Status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If Status = "ALL" Then
            strsql = "select a.*,b.SupName from PurchaseOrderHead a,Supplier b " & _
                     "where a.PoSupID=b.SupID and a.PoCompanyID=" & CompanyID & " order by PoDate desc"
        Else
            strsql = "select a.*,b.SupName from PurchaseOrderHead a,Supplier b " & _
                     "where a.PoStatus='" & Status & "' and a.PoSupID=b.SupID and a.PoCompanyID=" & CompanyID & " order by PoDate desc"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRHEAD")
        Try
            da.Fill(ds, "ORDRHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOrderHeader = ds
    End Function
    Public Function GetPendingOrderHeaderList(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select PoID,PoNumber,PoDate from PurchaseOrderHead  " & _
                 "where PoStatus='PENDING' and PoCompanyID=" & CompanyID & " order by PoDate desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRHEAD")
        Try
            da.Fill(ds, "ORDRHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPendingOrderHeaderList = ds
    End Function
    Public Function GetShipmentList(ByVal CompanyID As Integer, ByVal Status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select ShID,ShNumber,ShDate,ShRemark from ShipmentHead  " & _
                 "where ShStatus='" & Status & "' and ShCompanyID=" & CompanyID & " order by ShNumber desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPHEAD")
        Try
            da.Fill(ds, "SHIPHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentList = ds
    End Function
    Public Function GetInvoiceList(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal RptDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim RpDate As Date
        Try
            RpDate = Date.Parse(RptDate)
        Catch ex As Exception
            Err.Raise(666, "GetInvoiceList", "Invalid Date")
            Exit Function
        End Try
        strsql = "select InvID,InvNo,InvDateTime,InvStatus,InvGrossAmt,InvGdp,InvGda,InvNet from InvHead " & _
                     "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & _
                    " and day(InvDateTime)=" & RpDate.Day & " and month(InvDateTime)=" & RpDate.Month & _
                    " and year(InvDateTime)=" & RpDate.Year & " order by InvDateTime desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceList = ds
    End Function
    Public Function GetInvoiceListTotal(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal RptDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim RpDate As Date
        Try
            RpDate = Date.Parse(RptDate)
        Catch ex As Exception
            Err.Raise(666, "GetInvoiceListTotal", "Invalid Date")
            Exit Function
        End Try
        strsql = "select sum(InvGrossAmt) GrossAmt,sum(InvGda) DisAmt,sum(InvNet) NetAmt from InvHead " & _
                     "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & _
                    " and day(InvDateTime)=" & RpDate.Day & " and month(InvDateTime)=" & RpDate.Month & _
                    " and year(InvDateTime)=" & RpDate.Year & " group by day(InvDateTime)"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceListTotal = ds
    End Function
    Public Function GetWholesaleInvoiceHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from InvWHead " & _
                     "where InvID='" & InvoiceID & "'"
        Else
            strsql = "select * from InvWHead " & _
                     "where InvNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVHEAD")
        Try
            da.Fill(ds, "INVHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleInvoiceHeader = ds
    End Function
    Public Function GetWholesaleQuotationHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from QtnHead " & _
                     "where QtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from QtnHead " & _
                     "where QtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVHEAD")
        Try
            da.Fill(ds, "INVHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleQuotationHeader = ds
    End Function
    Public Function GetWholesaleReturnHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from RtnWHead " & _
                     "where RtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from RtnWHead " & _
                     "where RtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("RTNHEAD")
        Try
            da.Fill(ds, "RTNHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleReturnHeader = ds
    End Function
    Public Function GetWholesaleLpoHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from LpoHead " & _
                     "where LpoID='" & InvoiceID & "'"
        Else
            strsql = "select * from LpoHead " & _
                     "where LpoNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVHEAD")
        Try
            da.Fill(ds, "INVHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleLpoHeader = ds
    End Function
    Public Function GetInvoiceDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from InvData " & _
                     "where InvID='" & InvoiceID & "'"
        Else
            strsql = "select * from InvData " & _
                     "where InvNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceDetails = ds
    End Function
    Public Function GetWholesaleInvoiceDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from InvWData " & _
                     "where InvID='" & InvoiceID & "'"
        Else
            strsql = "select * from InvWData " & _
                     "where InvNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleInvoiceDetails = ds
    End Function

    Public Function GetWholesaleQuotationDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from QtnData " & _
                     "where QtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from QtnData " & _
                     "where QtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleQuotationDetails = ds
    End Function
    Public Function GetWholesaleReturnDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from RtnWData " & _
                     "where RtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from RtnWData " & _
                     "where RtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("RTNDATA")
        Try
            da.Fill(ds, "RTNDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleReturnDetails = ds
    End Function
    Public Function GetWholesaleLpoDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from LpoData " & _
                     "where LpoID='" & InvoiceID & "'"
        Else
            strsql = "select * from LpoData " & _
                     "where LpoNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleLpoDetails = ds
    End Function
    Public Function GetPurchaseRtnDetails(ByVal ReturnID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseRtnData " & _
                 "where PrID='" & ReturnID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPurchaseRtnDetails = ds
    End Function
    Public Function GetOrderDetails(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseOrderData " & _
                 "where PoID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRDATA")
        Try
            da.Fill(ds, "ORDRDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOrderDetails = ds
    End Function
    Public Function GetProductPendingOrders(ByVal CompanyID As Integer, ByVal ProductCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseOrderData " & _
                 "where PoProductCode='" & ProductCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRDATA")
        Try
            da.Fill(ds, "ORDRDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductPendingOrders = ds
    End Function
    Public Function GetShipmentDetails(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from ShipmentData " & _
                 "where ShID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPDATA")
        Try
            da.Fill(ds, "SHIPDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentDetails = ds
    End Function
    Public Function GetOrderHeaderData(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseOrderHead " & _
                 "where PoID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRHEAD")
        Try
            da.Fill(ds, "ORDRHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOrderHeaderData = ds
    End Function
    Public Function GetShipmentHeaderData(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from ShipmentHead " & _
                 "where ShID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPHEAD")
        Try
            da.Fill(ds, "SHIPHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentHeaderData = ds
    End Function
    Public Function GetShipmentByCurrency(ByVal CompanyID As Integer, ByVal CurrencyCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select ShID,ShNumber from ShipmentHead " & _
                "where ShID in (select distinct(shID) ShID " & _
                "from ShipmentData b,PurchaseOrderHead a " & _
                "where b.ShPoID=a.PoID and a.PoCurrency='" & CurrencyCode & "' " & _
                "and a.PoCompanyID=" & CompanyID & ") and " & _
                "shCompanyID=" & CompanyID & " and shStatus='SAVE'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPHEAD")
        Try
            da.Fill(ds, "SHIPHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentByCurrency = ds
    End Function
    Public Function GetPosCusData(ByVal ProfileID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PosCustomer " & _
                 "where ProfileID='" & ProfileID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PROFILE")
        Try
            da.Fill(ds, "PROFILE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPosCusData = ds
    End Function
    Public Function GetPosCustomers(ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If GroupID = 0 Then
            strsql = "select * from PosCustomer "
        Else
            strsql = "select * from PosCustomer " & _
                     "where CusGroup='" & GroupID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PROFILE")
        Try
            da.Fill(ds, "PROFILE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPosCustomers = ds
    End Function
    Public Function GetPosCusRequest(ByVal RequestID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select a.*,b.* from PosCusRequest a,PosCustomer b " & _
                    "where a.ReqProfileID = b.ProfileID and RequestID='" & RequestID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("REQUEST")
        Try
            da.Fill(ds, "REQUEST")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPosCusRequest = ds
    End Function
    Public Sub InsertProductData(ByVal CompanyID As Integer, ByVal BrandID As Integer, ByVal ManufProdCode As String, _
                ByVal ProductCode As String, ByVal ShortE As String, ByVal ShortA As String, _
                ByVal LongE As String, ByVal LongA As String, ByVal Group2 As Integer, _
                ByVal Group3 As Integer, ByVal Group4 As Integer, ByVal Group5 As Integer, _
                ByVal Price1 As String, ByVal Price2 As String, ByVal Price3 As String, _
                ByVal Markup1 As String, ByVal Markup2 As String, ByVal Markup3 As String, _
                ByVal SaleUnit As String, ByVal RolQty As String, ByVal LeadTime As String, _
                ByVal LeadTimePeriod As String, ByVal ItemType As Integer, ByVal OuterPackSize As String, _
                ByVal PackVolumeType As String, ByVal ImagePath As String, ByVal ActiveFlag As Integer, _
                ByVal PromotionFlag As Integer, ByVal KitFlag As Integer, ByVal WhFlag As Integer, _
                ByVal ShowroomsRolXml As String, ByVal BarCodesXml As String, ByVal ProdSupXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Insert into Product(CompanyCode,ProductCode,ProductType,ManufProductCode," & _
                "ShortDescriptionE,ShortDescriptionA,LongDescriptionE,LongDescriptionA," & _
                "Group1Code,Group2Code,Group3Code,Group4Code,Group5Code,FirstPrice,SecondPrice,ThirdPrice," & _
                "SaleUnit,FirstMarkupPercent,SecondMarkupPercent,ThirdMarkupPercent," & _
                "OuterPackSize,PackVolumeType,LeadTime,LeadTimeType,ImageFileName," & _
                "RolQty,ActiveFlag,OnPromotionFlag,KitFlag,WhProductFlag) " & _
                "Values(" & CompanyID & ",'" & ProductCode + "'," & GetNumDefault(ItemType) & "," & _
                "'" & ManufProdCode & "','" & ShortE & "','" & ShortA & "','" & LongE & "'," & _
                "'" & LongA & "'," & BrandID & "," & Group2 & "," & Group3 & "," & Group4 & "," & Group5 & "," & _
                GetNumDefault(Price1) & "," & GetNumDefault(Price2) & "," & GetNumDefault(Price3) & ",'" & _
                SaleUnit & "'," & GetNumDefault(Markup1) & "," & GetNumDefault(Markup2) & "," & GetNumDefault(Markup3) & "," & _
                "'" & OuterPackSize & "','" & PackVolumeType & "'," & GetNumDefault(LeadTime) & ",'" & LeadTimePeriod & "','" & ImagePath & "'," & _
                GetNumDefault(RolQty) & "," & ActiveFlag & "," & PromotionFlag & "," & KitFlag & "," & WhFlag & ") " & _
                "Select @@Identity "
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim ProdID As Integer
        Try
            ProdID = sqlCmd.ExecuteScalar()
        Catch ex As SqlException
            InsertError(ex.Procedure, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(ShowroomsRolXml)
        Dim ShowID As Integer
        Dim ShowRolQty As Decimal
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("Showroom")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            ShowID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            ShowRolQty = Integer.Parse(nodeList.Item(i).ChildNodes(1).InnerXml())
            strsql = "Insert into ShowroomRol (ProductID,ShowroomID,MinimumQty) " & _
                    "values(" & ProdID & "," & ShowID & "," & ShowRolQty & ")"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        Xdoc.LoadXml(ProdSupXml)
        Dim SupID As Integer
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("Supplier")
        For i = 0 To nodeList.Count - 1
            SupID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            strsql = "Insert into ProductSupplier (SupID,ProdID,CompanyID) " & _
                    "values(" & SupID & "," & ProdID & "," & CompanyID & ")"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        Xdoc.LoadXml(BarCodesXml)
        Dim BarCode As String
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("BarCode")
        For i = 0 To nodeList.Count - 1
            BarCode = nodeList.Item(i).InnerXml()
            strsql = "Insert into BarCode (ProductID,ProductBarCode) " & _
                    "values(" & ProdID & ",'" & BarCode & "')"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        sqlCon.Close()
    End Sub
    Public Function ProcessCusProdMatrix(ByVal InvXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(InvXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim CompanyID As Integer = RootNode.SelectSingleNode("InvoiceHead/CompanyID").InnerText
        Dim CustomerID As String = RootNode.SelectSingleNode("InvoiceHead/CustomerID").InnerText
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            Dim ProdID As String = nodeList.Item(i).ChildNodes(0).InnerText
            Dim ProdCode As String = nodeList.Item(i).ChildNodes(1).InnerText
            Dim mcount As Integer = 0
            sqlCmd.CommandText = "select count(*) from CustomerProduct with (NoLock) " & _
                "where CompanyID=" & CompanyID & " and CustomerID=" & CustomerID & " and ProductID=" & ProdID
            Try
                mcount = Integer.Parse(sqlCmd.ExecuteScalar)
            Catch exm As SqlException
                mcount = 0
            End Try
            If mcount = 0 Then
                strsql = "Insert into CustomerProduct (CompanyID,CustomerID,ProductID,CustomerProdCode) " & _
                        "Values (" & CompanyID & "," & CustomerID & "," & ProdID & "," & _
                        "'" & ProdCode & "')"
                sqlCmd.CommandText = strsql
            Else
                strsql = "Update CustomerProduct set CustomerProdCode='" & ProdCode & "' " & _
                        " where CompanyID=" & CompanyID & " and CustomerID=" & CustomerID & " and ProductID=" & ProdID
                sqlCmd.CommandText = strsql
            End If
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As SqlException
                InsertError("ProcessCusProdMatrix", ex.LineNumber, ex.Number, ex.ToString())
                Return "ERROR"
            End Try
        Next i
        sqlCon.Close()
        Return "OK"
    End Function
    Public Function ProcessProdWarehouseBin(ByVal InvXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(InvXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim WarehouseID As String = RootNode.SelectSingleNode("InvoiceHead/WarehouseID").InnerText
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            Dim ProdID As String = nodeList.Item(i).ChildNodes(0).InnerText
            Dim BinName As String = nodeList.Item(i).ChildNodes(1).InnerText
            If BinName.Trim <> "" Then
                BinName = BinName.ToUpper
            Else
                BinName = Space(15)
            End If
            strsql = "Select count(*) from ProductWarehouseBin " & _
                    "where WarehouseID=" & WarehouseID & " and ProductID=" & ProdID
            sqlCmd.CommandText = strsql
            If sqlCmd.ExecuteScalar = 0 Then
                strsql = "Insert into ProductWarehouseBin (WarehouseID,ProductID,BinName) " & _
                        "Values (" & WarehouseID & "," & ProdID & "," & _
                        "'" & BinName & "')"
            Else
                strsql = "Update ProductWarehouseBin set BinName='" & BinName & "' " & _
                        "where WarehouseID=" & WarehouseID & " and ProductID=" & ProdID
            End If
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As SqlException
                InsertError("ProcessProdWarehouseBin", ex.LineNumber, ex.Number, ex.ToString())
                Return "ERROR"
            End Try
        Next i
        sqlCon.Close()
        Return "OK"
    End Function
    Public Sub UpdateProductData(ByVal ProdID As Integer, ByVal BrandID As Integer, ByVal ManufProdCode As String, _
                ByVal ProductCode As String, ByVal ShortE As String, ByVal ShortA As String, _
                ByVal LongE As String, ByVal LongA As String, ByVal Group2 As Integer, _
                ByVal Group3 As Integer, ByVal Group4 As Integer, ByVal Group5 As Integer, _
                ByVal Price1 As String, ByVal Price2 As String, ByVal Price3 As String, _
                ByVal Markup1 As String, ByVal Markup2 As String, ByVal Markup3 As String, _
                ByVal SaleUnit As String, ByVal RolQty As String, ByVal LeadTime As String, _
                ByVal LeadTimePeriod As String, ByVal ItemType As Integer, ByVal OuterPackSize As String, _
                ByVal PackVolumeType As String, ByVal ImagePath As String, ByVal ActiveFlag As Integer, _
                ByVal PromotionFlag As Integer, ByVal KitFlag As Integer, ByVal WhFlag As Integer, _
                ByVal ShowroomsRolXml As String, ByVal BarCodesXml As String, ByVal ProdSupXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Update Product set " & _
            "ProductCode='" & ProductCode & "',ProductType=" & GetNumDefault(ItemType) & "," & _
            "ManufProductCode='" & ManufProdCode & "',ShortDescriptionE='" & ShortE & "'," & _
            "ShortDescriptionA='" & ShortA & "',LongDescriptionE='" & LongE & "'," & _
            "LongDescriptionA='" & LongA & "',Group1Code=" & BrandID & ",Group2Code=" & Group2 & "," & _
            "Group3Code=" & Group3 & ",Group4Code=" & Group4 & ",Group5Code=" & Group5 & "," & _
            "FirstPrice=" & GetNumDefault(Price1) & "," & "SecondPrice=" & GetNumDefault(Price2) & "," & _
            "ThirdPrice=" & GetNumDefault(Price3) & ",FirstMarkupPercent=" & GetNumDefault(Markup1) & "," & _
            "SecondMarkupPercent=" & GetNumDefault(Markup2) & ",ThirdMarkupPercent=" & GetNumDefault(Markup3) & "," & _
            "SaleUnit='" & SaleUnit & "',OuterPackSize='" & OuterPackSize & "',PackVolumeType='" & PackVolumeType & "'," & _
            "LeadTime=" & GetNumDefault(LeadTime) & ",LeadtimeType='" & LeadTimePeriod & "',ImageFileName='" & ImagePath & "'," & _
            "RolQty=" & GetNumDefault(RolQty) & ",ActiveFlag=" & ActiveFlag & ",OnPromotionFlag=" & PromotionFlag & "," & _
            "KitFlag=" & KitFlag & ",WhProductFlag=" & WhFlag & _
            " where ProductID=" & ProdID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(ShowroomsRolXml)
        Dim ShowID As Integer
        Dim ShowRolQty As Decimal
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("Showroom")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            ShowID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            ShowRolQty = Integer.Parse(nodeList.Item(i).ChildNodes(1).InnerXml())
            strsql = "Update ShowroomRol set MinimumQty=" & ShowRolQty & _
                    " where ProductID=" & ProdID & " and ShowroomID=" & ShowID
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i

        strsql = "Delete from BarCode where ProductID=" & ProdID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()

        Xdoc.LoadXml(BarCodesXml)
        Dim BarCode As String
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("BarCode")
        For i = 0 To nodeList.Count - 1
            BarCode = nodeList.Item(i).InnerXml()
            strsql = "Insert into BarCode (ProductID,ProductBarCode) " & _
                    "values(" & ProdID & ",'" & BarCode & "')"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        strsql = "Delete from ProductSupplier where ProductID=" & ProdID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        '
        Xdoc.LoadXml(ProdSupXml)
        Dim SupID As Integer
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("Supplier")
        For i = 0 To nodeList.Count - 1
            SupID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            strsql = "Insert into ProductSupplier (SupID,ProdID,CompanyID) " & _
                    "values(" & SupID & "," & ProdID & "," & CompanyID & ")"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        sqlCon.Close()
    End Sub
    Public Sub UpdateGroupData(ByVal GroupID As Integer, ByVal GroupCode As String, _
                ByVal GroupName As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Update CustomerGroup set " & _
            "GroupCode='" & GroupCode & "',GroupName='" & GroupName & "'" & _
            " where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
    End Sub
    Public Sub UpdateDocData(ByVal DocID As Integer, ByVal DocCode As String, _
                 ByVal DocName As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Update FasDoc set " & _
            "DOC_ID='" & DocCode & "',Doc_Name='" & DocName & "'" & _
            " where ID=" & DocID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
    End Sub
    Public Sub UpdateAssetGroupData(ByVal GroupID As Integer, _
                 ByVal GroupName As String, ByVal FAmain As String, ByVal FaSub As String, _
                                ByVal AdMain As String, ByVal AdSub As String, ByVal ExMain As String, _
                                ByVal ExSub As String, ByVal PcMain As String, ByVal PcSub As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        Dim GLdata As New GLdataLogic
        Dim FaID, DpID, ExID, PcID As Integer
        FaID = GLdata.GetAccountIdFromNumber(CompanyID, FAmain, FaSub)
        DpID = GLdata.GetAccountIdFromNumber(CompanyID, AdMain, AdSub)
        ExID = GLdata.GetAccountIdFromNumber(CompanyID, ExMain, ExSub)
        PcID = GLdata.GetAccountIdFromNumber(CompanyID, PcMain, PcSub)
        GLdata = Nothing
        strsql = "Update AssetGroup set " & _
            "GroupName='" & GroupName & "',FaAccountID='" & FaID & "',AdAccountID='" & DpID & "',DpAccountID='" & ExID & "',PcAccountID='" & PcID & "' " & _
            " where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
    End Sub
    Public Function GetNumDefault(ByVal NumericField As String)
        If NumericField.ToString.Trim = "" Then
            Return "0"
        Else
            Return NumericField.Replace(",", "")
        End If
    End Function
    Public Sub DeleteProduct(ByVal ProdID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Product where ProductID=" & ProdID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteCustomer(ByVal CustomerID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Customer where CusID=" & CustomerID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteEmployee(ByVal EmployeeID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Employee where EmpID=" & EmployeeID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteAsset(ByVal AssetID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Asset where AssetID=" & AssetID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteCustomerGroup(ByVal GroupID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from CustomerGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteDocumentType(ByVal DocID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from FasDoc where ID=" & DocID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteAssetGroup(ByVal GroupID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from AssetGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertCustomerGroup(ByVal GroupCode As String, ByVal GroupName As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into CustomerGroup (GroupCode,GroupName) " & _
                "values('" & GroupCode & "','" & GroupName & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertCompany(ByVal CompanyName As String, ByVal PoPrefix As String, _
                        ByVal GrvPrefix As String, ByVal RtnPrefix As String, _
                        ByVal TrvPrefix As String, ByVal AdjPrefix As String, _
                        ByVal PoNumber As String, ByVal GrvNumber As String, _
                        ByVal RtnNumber As String, ByVal TrvNumber As String, _
                        ByVal AdjNumber As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into Company (CompanyName,NextPoNumber,PoPrefix,NextGrvNumber,GrvPrefix," & _
                    "NextPurhRtnNumber,PurhRtnPrefix,NextTrvNumber,TrvPrefix," & _
                    "NextAdjNumber,AdjPrefix) " & _
                "values('" & CompanyName & "'," & PrefixInteger(PoNumber) & ",'" & PoPrefix & "'," & _
                PrefixInteger(GrvNumber) & ",'" & GrvPrefix & "'," & PrefixInteger(RtnNumber) & ",'" & _
                RtnPrefix & "'," & PrefixInteger(TrvNumber) & ",'" & TrvPrefix & "'," & _
                PrefixInteger(AdjNumber) & ",'" & AdjPrefix & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertDepartment(ByVal DepartmentName As String, ByVal PoPrefix As String, _
                        ByVal GrvPrefix As String, ByVal RtnPrefix As String, _
                        ByVal TrvPrefix As String, ByVal AdjPrefix As String, _
                        ByVal PoNumber As String, ByVal GrvNumber As String, _
                        ByVal RtnNumber As String, ByVal TrvNumber As String, _
                        ByVal AdjNumber As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into Company (CompanyName,NextPoNumber,PoPrefix,NextGrvNumber,GrvPrefix," & _
                    "NextPurhRtnNumber,PurhRtnPrefix,NextTrvNumber,TrvPrefix," & _
                    "NextAdjNumber,AdjPrefix) " & _
                "values('" & DepartmentName & "'," & PrefixInteger(PoNumber) & ",'" & PoPrefix & "'," & _
                PrefixInteger(GrvNumber) & ",'" & GrvPrefix & "'," & PrefixInteger(RtnNumber) & ",'" & _
                RtnPrefix & "'," & PrefixInteger(TrvNumber) & ",'" & TrvPrefix & "'," & _
                PrefixInteger(AdjNumber) & ",'" & AdjPrefix & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub

    Public Sub InsertDocumentType(ByVal DocCode As String, ByVal DocName As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into FasDoc (Doc_ID,Doc_Name) " & _
                "values('" & DocCode & "','" & DocName & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertAssetGroup(ByVal GroupName As String, ByVal FAmain As String, ByVal FaSub As String, _
                                ByVal AdMain As String, ByVal AdSub As String, ByVal ExMain As String, ByVal ExSub As String, _
                                ByVal PcMain As String, ByVal PcSub As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim GLdata As New GLdataLogic
        Dim FaID, DpID, ExID, PcID As Integer
        FaID = GLdata.GetAccountIdFromNumber(CompanyID, FAmain, FaSub)
        DpID = GLdata.GetAccountIdFromNumber(CompanyID, AdMain, AdSub)
        ExID = GLdata.GetAccountIdFromNumber(CompanyID, ExMain, ExSub)
        PcID = GLdata.GetAccountIdFromNumber(CompanyID, PcMain, PcSub)

        strsql = "Insert into AssetGroup (GroupName,FaAccountID,AdAccountID,DpAccountID,PcAccountID) " & _
                "values('" & GroupName & "'," & FaID & "," & DpID & "," & ExID & "," & PcID & ")"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Function InsertCustomerDetails(ByVal CompanyID As Integer, ByVal CusCode As String, ByVal GroupID As Integer, ByVal CusName As String, _
                ByVal Adr1 As String, ByVal Adr2 As String, ByVal Dadr1 As String, ByVal Dadr2 As String, _
                ByVal Email As String, ByVal Website As String, ByVal Phone As String, _
                ByVal Fax As String, ByVal Contact As String, ByVal SalesmanID As Integer, ByVal CusXml As String, ByVal ContractCode As String, ByVal ContractAmt As Decimal, ByVal StartDate As Date, ByVal EndDate As Date, ByVal ActStartDate As Date, ByVal ContractInfo As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        Dim DateStart, DateEnd, ActDateStart As String

        DateStart = StartDate.ToString("yyyy-MM-dd")
        DateEnd = EndDate.ToString("yyyy-MM-dd")
        ActDateStart = ActStartDate.ToString("yyyy-MM-dd")

        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        strsql = "Select CusID from Customer where CusCode='" & CusCode & "' "
        sqlCmd.CommandText = strsql
        Dim mCusID As Integer = 0
        Try
            mCusID = sqlCmd.ExecuteScalar()
        Catch ex As Exception

        End Try
        If mCusID > 0 Then
            Return "EXIST"
            Exit Function
        End If
        strsql = "Insert into Customer(CompanyID,CusCode,CusGroupID,CusName," & _
                "CusAdr1,CusAdr2,CusDadr1,CusDadr2,CusEmail,CusWebUrl,CusTelephone,CusFax,CusContact,SalesmanID,ContractCode,ContractAmt,StartDate,EndDate,ActStartDate,ContractInfo) " & _
                "values(" & CompanyID & ",'" & CusCode & "'," & GroupID & "," & "'" & CusName & "','" & Adr1 & "','" & Adr2 & "','" & Dadr1 & "','" & Dadr2 & "'," & "'" & Email & "','" & Website & "','" & Phone & "'," & _
                "'" & Fax & "','" & Contact & "'," & SalesmanID & ",'" & ContractCode & "'," & ContractAmt & ",'" & DateStart & "','" & DateEnd & "','" & ActDateStart & "','" & ContractInfo & "' ) " & "select @@identity"
        sqlCmd.CommandText = strsql
        Dim CusID As Integer
        Try
            CusID = sqlCmd.ExecuteScalar
        Catch ex As Exception
            Return "ERROR"
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CusXml)
        Dim IncludeCus As Boolean
        Dim CrLimit As Decimal
        Dim CrPeriod As String
        Dim ThisCompany As Integer
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            IncludeCus = nodeList.Item(i).ChildNodes(1).InnerXml()
            ThisCompany = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            CrLimit = nodeList.Item(i).ChildNodes(2).InnerXml()
            CrPeriod = nodeList.Item(i).ChildNodes(3).InnerXml()
            'If CrLimit = "" Then
            '    CrLimit = 0
            'End If
            'If CrPeriod = "" Then
            '    CrPeriod = 0
            'End If

            If IncludeCus = True Then
                strsql = "Insert into CompanyCustomer (CusID,CompanyID,CreditLimit,CrPeriod) " & _
                        "values(" & CusID & "," & ThisCompany & "," & CrLimit & ",'" & CrPeriod & "')"
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            End If
        Next i
        sqlCon.Close()
        Return "OK"
    End Function
    Public Function InsertEmployeeDetails(ByVal CompanyID As Integer, ByVal PfNo As String, ByVal PostID As Integer, _
            ByVal EmpName As String, ByVal NationID As Integer, _
            ByVal fAdr1 As String, ByVal fAdr2 As String, ByVal fAdr3 As String, ByVal Ladr1 As String, ByVal Ladr2 As String, ByVal Ladr3 As String, _
            ByVal Email As String, ByVal fPhone As String, ByVal Lphone As String, ByVal DOB As String, _
            ByVal Remarks As String, ByVal ApptDate As String, ByVal PasNo As String, ByVal ResNo As String, ByVal PasExpiry As String, _
            ByVal ResExpiry As String, ByVal Sex As Integer, ByVal Married As Integer, ByVal PhotoPath As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        strsql = "Select EmpID from Employee where ltrim(rtrim(PfNo))='" & PfNo & "' "
        sqlCmd.CommandText = strsql
        Dim mCusID As Integer = 0
        Try
            mCusID = sqlCmd.ExecuteScalar()
        Catch ex As Exception

        End Try
        If mCusID > 0 Then
            Return "EXIST"
            Exit Function
        End If
        strsql = "Insert into Employee(EmpCompanyID,EmpPfNO,EmpName," & _
                "EmpPermAdr1,EmpPermAdr2,EmpPermAdr3,EmpPresentAdr1,EmpPresentAdr2,EmpPresentAdr3," & _
                "EmpPermPhone,EmpPresentPhone,EmpEmail,EmpMaritalStatus,EmpSex,EmpDesignationID," & _
                "EmpNationalityID,EmpDOB,EmpAppointmentDate,EmpPPno,EmpResidenceNo,EmpPPexpiryDate,EmpResidenceExpiry,EmpRemarks) " & _
                "values(" & CompanyID & ",'" & PfNo & "','" & EmpName & "','" & fAdr1 & "','" & fAdr2 & "'," & _
                "'" & fAdr3 & "','" & Ladr1 & "','" & Ladr2 & "','" & Ladr3 & "','" & fPhone & "'," & _
                "'" & Lphone & "','" & Email & "'," & Married & "," & Sex & "," & PostID & "," & NationID & "," & _
                "'" & ValidDate(DOB) & "','" & ValidDate(ApptDate) & "','" & PasNo & "','" & ResNo & "','" & ValidDate(PasExpiry) & "','" & ValidDate(ResExpiry) & "','" & Remarks & "') " & _
                "select @@identity "
        sqlCmd.CommandText = strsql
        Dim EmpID As Integer
        Try
            EmpID = sqlCmd.ExecuteScalar
            If PhotoPath.Trim <> "" Then
                Dim oImg As FileStream
                Dim oBinaryReader As BinaryReader
                Dim oImgByteArray As Byte()
                oImg = New FileStream(PhotoPath, FileMode.Open, FileAccess.Read)
                oBinaryReader = New BinaryReader(oImg)
                oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
                Dim myParameter As SqlParameter = New SqlParameter("@Image", SqlDbType.Image, oImg.Length)
                myParameter.Value = oImgByteArray
                oBinaryReader.Close()
                oImg.Close()
                strsql = "Insert into EmployeePhoto " & _
                         "(EmpID,EmpPhoto) " & _
                         "Values(" & EmpID & ",@Image)"
                sqlCmd.CommandText = strsql
                sqlCmd.Parameters.Add(myParameter)
                sqlCmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            Return "ERROR"
        End Try
        sqlCon.Close()
        Return "OK"
    End Function
    Public Function UpdateEmployeeDetails(ByVal CompanyID As Integer, ByVal EditEmpID As Integer, ByVal PfNo As String, ByVal PostID As Integer, _
        ByVal EmpName As String, ByVal NationID As Integer, _
        ByVal fAdr1 As String, ByVal fAdr2 As String, ByVal fAdr3 As String, ByVal Ladr1 As String, ByVal Ladr2 As String, ByVal Ladr3 As String, _
        ByVal Email As String, ByVal fPhone As String, ByVal Lphone As String, ByVal DOB As String, _
        ByVal Remarks As String, ByVal ApptDate As String, ByVal PasNo As String, ByVal ResNo As String, ByVal PasExpiry As String, _
        ByVal ResExpiry As String, ByVal Sex As Integer, ByVal Married As Integer, ByVal PhotoPath As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        strsql = "Select EmpID from Employee where ltrim(rtrim(PfNo))='" & PfNo & "' and EmpID<>" & EditEmpID
        sqlCmd.CommandText = strsql
        Dim mCusID As Integer = 0
        Try
            mCusID = sqlCmd.ExecuteScalar()
        Catch ex As Exception

        End Try
        If mCusID > 0 Then
            Return "EXIST"
            Exit Function
        End If
        strsql = "Update Employee set " & _
                "EmpCompanyID=" & CompanyID & ",EmpPfNO='" & PfNo & "',EmpName='" & EmpName & "'," & _
                "EmpPermAdr1='" & fAdr1 & "',EmpPermAdr2='" & fAdr2 & "',EmpPermAdr3='" & fAdr3 & "'," & _
                "EmpPresentAdr1='" & Ladr1 & "',EmpPresentAdr2='" & Ladr2 & "',EmpPresentAdr3='" & Ladr3 & "'," & _
                "EmpPermPhone='" & fPhone & "',EmpPresentPhone='" & Lphone & "',EmpEmail='" & Email & "'," & _
                "EmpMaritalStatus=" & Married & ",EmpSex=" & Sex & ",EmpDesignationID=" & PostID & "," & _
                "EmpNationalityID=" & NationID & ",EmpDOB='" & ValidDate(DOB) & "',EmpAppointmentDate='" & ValidDate(ApptDate) & "'," & _
                "EmpPPno='" & PasNo & "',EmpResidenceNo='" & ResNo & "',EmpPPexpiryDate='" & ValidDate(PasExpiry) & "'," & _
                "EmpResidenceExpiry='" & ValidDate(ResExpiry) & "',EmpRemarks='" & Remarks & "' " & _
                "where EmpID=" & EditEmpID
        sqlCmd.CommandText = strsql
        Try
            sqlCmd.ExecuteNonQuery()
            If PhotoPath.Trim <> "" Then
                Dim oImg As FileStream
                Dim oBinaryReader As BinaryReader
                Dim oImgByteArray As Byte()
                oImg = New FileStream(PhotoPath, FileMode.Open, FileAccess.Read)
                oBinaryReader = New BinaryReader(oImg)
                oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
                Dim myParameter As SqlParameter = New SqlParameter("@Image", SqlDbType.Image, oImg.Length)
                myParameter.Value = oImgByteArray
                oBinaryReader.Close()
                oImg.Close()
                strsql = "Insert into EmployeePhoto " & _
                         "(EmpID,EmpPhoto) " & _
                         "Values(" & EditEmpID & ",@Image)"
                sqlCmd.CommandText = strsql
                sqlCmd.Parameters.Add(myParameter)
                sqlCmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            Return "ERROR"
        End Try
        sqlCon.Close()
        Return "OK"
    End Function
    Public Sub InsertAssetDetails(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal LocID As Integer, ByVal AssetName As String, _
                 ByVal FaMainAccount As String, ByVal FaSubAccount As String, ByVal DpMainAccount As String, ByVal DpSubAccount As String, _
                 ByVal ExMainAccount As String, ByVal ExSubAccount As String, ByVal PcMainAccount As String, ByVal PcSubAccount As String, ByVal PurchaseDate As String, _
                 ByVal PurchaseAmount As String, ByVal SupplierName As String, ByVal Invoice As String, _
                 ByVal LifeInMonths As String, ByVal DprPercent As String, ByVal CurrentValue As String, _
                 ByVal Remarks As String, ByVal Brand As String, ByVal Code As String, ByVal Voucher As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim GLdata As New GLdataLogic
        Dim FaID, DpID, ExID, PcID As Integer
        FaID = GLdata.GetAccountIdFromNumber(CompanyID, FaMainAccount, FaSubAccount)
        DpID = GLdata.GetAccountIdFromNumber(CompanyID, DpMainAccount, DpSubAccount)
        ExID = GLdata.GetAccountIdFromNumber(CompanyID, ExMainAccount, ExSubAccount)
        PcID = GLdata.GetAccountIdFromNumber(CompanyID, PcMainAccount, PcSubAccount)

        strsql = "Insert into Asset (CompanyID,GroupID,DptID,AssetName," & _
                "FaAccountID,AdAccountID,DpAccountID,PcAccountID,PurchaseDate,PurchaseAmount,SupplierName,InvoiceNumber,LifeInMonths,DprcnPercent,CurrentValue,Remarks,AssetBrand,AssetNumber,Voucher) " & _
                "values(" & CompanyID & "," & GroupID & "," & LocID & ",'" & AssetName & "'," & _
                "'" & FaID & "','" & DpID & "','" & ExID & "','" & PcID & "','" & ValidDate(PurchaseDate) & "','" & PrefixInteger(PurchaseAmount) & "'," & _
                "'" & SupplierName & "','" & Invoice & "','" & PrefixInteger(LifeInMonths) & "'," & _
                "'" & PrefixInteger(DprPercent) & "','" & PrefixInteger(CurrentValue) & "','" & Remarks & "','" & Brand & "','" & Code & "','" & Voucher & "') " & _
                "select @@identity"
        sqlCon.Open()
        Dim AssetID As Integer
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        AssetID = sqlCmd.ExecuteScalar
        sqlCon.Close()
    End Sub
    Public Sub UpdateAssetID(ByVal GID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select ASSETID From AssetGroup where GROUPID = " & GID
        'MessageBox.Show(strsql)
        sqlCon.Open()
        Dim AssetIDS As Integer
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        'AssetIDS = sqlCmd.ExecuteScalar
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
            AssetIDS = CInt(ds.Tables("ASSET").Rows(0)(0)) + 1
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        Try

            sqlCon.Open()
            strsql = "Update AssetGroup set ASSETID=" & AssetIDS & " where GROUPID=" & GID
            'MessageBox.Show(strsql)
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub
    Public Sub InsertAssetTransfer(ByVal AssetID As Integer, ByVal FromLocID As Integer, ByVal ToLocID As Integer, _
                  ByVal TransferDate As String, ByVal Remarks As String, ByVal TrfRef As String, ByVal HO As String, _
                  ByVal FW As String, ByVal SHW As String, ByVal UTG As String, ByVal TPG As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        strsql = "Insert into AssetTransferHistory (AssetID,FromLocationID,ToLocationID," & _
                "TransferDate,Remarks,TrfrRef) " & _
                "values(" & AssetID & "," & FromLocID & "," & ToLocID & "," & _
                "'" & ValidDate(TransferDate) & "','" & Remarks & "','" & TrfRef & "') "
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        strsql = "Update Asset set DptID=" & ToLocID & ",HOPercentage=" & HO & ",FWPercentage=" & FW & "," & _
        "SHWPercentage=" & SHW & ",UTGPercentage=" & UTG & ",TPGPercentage=" & TPG & " where AssetID=" & AssetID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub UpdateAssetDetails(ByVal CompanyID As Integer, ByVal AssetID As Integer, ByVal GroupID As Integer, ByVal LocID As Integer, ByVal AssetName As String, _
                  ByVal FaMainAccount As String, ByVal FaSubAccount As String, ByVal DpMainAccount As String, ByVal DpSubAccount As String, _
                  ByVal ExMainAccount As String, ByVal ExSubAccount As String, ByVal PcMainAccount As String, ByVal PcSubAccount As String, ByVal PurchaseDate As String, _
                  ByVal PurchaseAmount As String, ByVal SupplierName As String, ByVal Invoice As String, _
                  ByVal LifeInMonths As String, ByVal DprPercent As String, ByVal CurrentValue As String, _
                  ByVal Remarks As String, ByVal Brand As String, ByVal Code As String, ByVal Voucher As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim GLdata As New GLdataLogic
        Dim FaID, DpID, ExID, PcID As Integer
        FaID = GLdata.GetAccountIdFromNumber(CompanyID, FaMainAccount, FaSubAccount)
        DpID = GLdata.GetAccountIdFromNumber(CompanyID, DpMainAccount, DpSubAccount)
        ExID = GLdata.GetAccountIdFromNumber(CompanyID, ExMainAccount, ExSubAccount)
        PcID = GLdata.GetAccountIdFromNumber(CompanyID, PcMainAccount, PcSubAccount)
        strsql = "Update Asset set GroupID=" & GroupID & ",DptID=" & LocID & ",AssetName='" & AssetName & "'," & _
            "FaAccountID=" & FaID & ",AdAccountID=" & DpID & ",DpAccountID=" & ExID & ",PcAccountID=" & PcID & "," & _
            "PurchaseDate='" & ValidDate(PurchaseDate) & "',PurchaseAmount='" & PrefixInteger(PurchaseAmount) & "'," & _
            "SupplierName='" & SupplierName & "',InvoiceNumber='" & Invoice & "',LifeInMonths='" & LifeInMonths & "'," & _
            "DprcnPercent='" & PrefixInteger(DprPercent) & "',CurrentValue='" & PrefixInteger(CurrentValue) & "'," & _
            "Remarks='" & Remarks & "',AssetBrand='" & Brand & "',AssetNumber='" & Code & "',Voucher='" & Voucher & "' " & _
            "where AssetID=" & AssetID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Function GetAssetsSchedule(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal Status As String, ByVal LocationID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If LocationID > 0 Then
            If GroupID = 0 Then
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b where ltrim(rtrim(AssetStatus))='" & Status & "' " & _
                        "and AssetLocationID=" & LocationID & " and a.DptID=b.AssetLocationID order by a.AssetNumber"
            Else
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where AssetLocationID=" & LocationID & " and ltrim(rtrim(AssetStatus))='" & Status & "' and a.DptID=b.AssetLocationID and a.GroupID=" & GroupID & " " & _
                        "order by a.AssetNumber"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b where ltrim(rtrim(AssetStatus))='" & Status & "' " & _
                        "and a.DptID=b.AssetLocationID order by a.AssetNumber"
            Else
                strsql = "select a.*,b.AssetLocationName " & _
                        "from Asset a,AssetLocation b " & _
                        "where ltrim(rtrim(AssetStatus))='" & Status & "' and  a.GroupID=" & GroupID & " " & _
                        "and a.DptID=b.AssetLocationID order by a.AssetNumber"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetsSchedule = ds
    End Function
    Public Function GetAllAssetLocations() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.AssetLocationID,a.AssetLocationName,a.AssetACcode " & _
                "from AssetLocation a  " & _
                " order by AssetLocationName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllAssetLocations = ds
    End Function
    Public Sub DeleteAssetLocation(ByVal LocationID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from AssetLocation where AssetLocationID=" & LocationID & _
            " and AssetLocationID not in (Select DptID from Asset)"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertAssetLocation(ByVal LocationName As String, ByVal LocationCode As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into AssetLocation (AssetLocationName,AssetACcode) " & _
                "values('" & LocationName & "','" & LocationCode & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Function GetAssetLocationDetails(ByVal LocationID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from AssetLocation where AssetLocationID=" & LocationID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAssetLocationDetails = ds
    End Function
    Public Sub UpdateAssetLocationData(ByVal LocationID As Integer, _
              ByVal LocationName As String, ByVal code As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Update AssetLocation set " & _
            "AssetLocationName='" & LocationName & "',AssetACcode='" & code & "'" & _
            " where AssetLocationID=" & LocationID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
    End Sub
    Public Sub InsertAssetMaint(ByVal AssetID As Integer, ByVal LocID As Integer, _
               ByVal MaintDate As String, ByVal Remarks As String, ByVal InvoiceNo As String, ByVal InvAmount As Decimal)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        strsql = "Insert into AssetMaintHistory (AssetID,LocationID," & _
                "MaintDate,Remarks,InvoiceNo,InvoiceAmount) " & _
                "values(" & AssetID & "," & LocID & "," & _
                "'" & ValidDate(MaintDate) & "','" & Remarks & "','" & InvoiceNo & "','" & InvAmount & "') "
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        strsql = "Update Asset set DptID=" & LocID & " where AssetID=" & AssetID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Function CalculateDepreciation(ByVal GroupID As Integer, ByVal LocationID As Integer, ByVal DDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        Dim dateFrom As Date
        Try
            dateFrom = Date.Parse(DDate)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Function
        End Try
        Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        sFdate = sFdate + " 23:59"

        'SUM(CASE month(b.TrxDate) WHEN 1 THEN c.TrxQty ELSE 0 END) AS JanQty,
        'max(DateDiff(Day,y.createdate,getdate())) as AgeF
        'DATEDIFF(Month, PurchaseDate,'" & ProcessDate & "') as TotalLife

        'strsql = "select AssetID,AssetName,AssetNumber,PurchaseDate,AssetStatus," & _
        '           "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
        '           "PurchaseAmount/LifeInMonths MonthlyAmount,HOPercentage,FWPercentage" & _
        '           ",SHWPercentage,UTGPercentage,TPGPercentage," & _
        '           "((PurchaseAmount/LifeInMonths)*(HOPercentage/100)) HODep,  " & _
        '           "((PurchaseAmount/LifeInMonths)*(FWPercentage/100)) FWDep,  " & _
        '           "((PurchaseAmount/LifeInMonths)*(SHWPercentage/100)) SHWDep,  " & _
        '           "((PurchaseAmount/LifeInMonths)*(UTGPercentage/100)) UTGDep,  " & _
        '           "((PurchaseAmount/LifeInMonths)*(TPGPercentage/100)) TPGDep  " & _
        '           " from Asset " & _
        '           "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' " & _
        '           "order by AssetNumber"

        If LocationID = 0 Then
            If GroupID = 0 Then
                strsql = "select AssetID,AssetName,AssetNumber,convert(char(10),PurchaseDate,103) PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue ELSE PurchaseAmount/LifeInMonths END as MonthlyAmount," & _
                        "HOPercentage,FWPercentage,SHWPercentage,UTGPercentage,TPGPercentage," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(HOPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(HOPercentage/100) END as HODep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(FWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(FWPercentage/100) END as FWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(SHWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(SHWPercentage/100) END as SHWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(UTGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(UTGPercentage/100) END as UTGDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(TPGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(TPGPercentage/100) END as TPGDep" & _
                        " from Asset " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' " & _
                        "order by AssetNumber"
            Else
                strsql = "select AssetID,AssetName,AssetNumber,convert(char(10),PurchaseDate,103) PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue ELSE PurchaseAmount/LifeInMonths END as MonthlyAmount,HOPercentage,FWPercentage" & _
                        ",SHWPercentage,UTGPercentage,TPGPercentage," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(HOPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(HOPercentage/100) END as HODep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(FWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(FWPercentage/100) END as FWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(SHWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(SHWPercentage/100) END as SHWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(UTGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(UTGPercentage/100) END as UTGDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(TPGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(TPGPercentage/100) END as TPGDep" & _
                        " from Asset " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' and GroupID=" & GroupID & " " & _
                        "order by AssetNumber"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select AssetID,AssetName,AssetNumber,convert(char(10),PurchaseDate,103) PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue ELSE PurchaseAmount/LifeInMonths END as MonthlyAmount,HOPercentage,FWPercentage" & _
                        ",SHWPercentage,UTGPercentage,TPGPercentage," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(HOPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(HOPercentage/100) END as HODep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(FWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(FWPercentage/100) END as FWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(SHWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(SHWPercentage/100) END as SHWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(UTGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(UTGPercentage/100) END as UTGDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(TPGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(TPGPercentage/100) END as TPGDep" & _
                        " from Asset a " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' " & _
                        "and DptID=" & LocationID & " order by AssetNumber"
            Else
                strsql = "select AssetID,AssetName,AssetNumber,convert(char(10),PurchaseDate,103) PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue ELSE PurchaseAmount/LifeInMonths END as MonthlyAmount,HOPercentage,FWPercentage" & _
                        ",SHWPercentage,UTGPercentage,TPGPercentage," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(HOPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(HOPercentage/100) END as HODep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(FWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(FWPercentage/100) END as FWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(SHWPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(SHWPercentage/100) END as SHWDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(UTGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(UTGPercentage/100) END as UTGDep," & _
                        "CASE DateDiff(Month,PurchaseDate,'" & sFdate & "') WHEN LifeInMonths THEN CurrentValue*(TPGPercentage/100) ELSE (PurchaseAmount/LifeInMonths)*(TPGPercentage/100) END as TPGDep" & _
                        " from Asset " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' and GroupID=" & GroupID & " " & _
                        "and DptID=" & LocationID & " order by AssetNumber"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim drow As DataRow
        Dim cryfwrd As Decimal
        For Each drow In ds.Tables("ASSET").Rows
            drow("HODep") = CDbl(drow("HODep")).ToString("#0.000")
            drow("FWDep") = CDbl(drow("FWDep")).ToString("#0.000")
            drow("SHWDep") = CDbl(drow("SHWDep")).ToString("#0.000")
            drow("UTGDep") = CDbl(drow("UTGDep")).ToString("#0.000")
            drow("TPGDep") = CDbl(drow("TPGDep")).ToString("#0.000")

            If CDbl(drow("CurrentValue")) - CDbl(drow("MonthlyAmount")) < CDbl(drow("MonthlyAmount")) Then
                drow("MonthlyAmount") = CDbl(drow("MonthlyAmount")) + CDbl(CDbl(drow("CurrentValue")) - CDbl(drow("MonthlyAmount")))
            End If

            cryfwrd = CDbl(CDbl(drow("MonthlyAmount")) - (CDbl(drow("HODep")) + CDbl(drow("FWDep")) + CDbl(drow("SHWDep")) + CDbl(drow("UTGDep")) + CDbl(drow("TPGDep")))).ToString("#0.000")
            'MessageBox.Show(drow("AssetName") & cryfwrd)
            If cryfwrd > 0 Then
                If drow("FWDep") > 0 Then
                    'MessageBox.Show(
                    drow("FWDep") = CDbl(drow("FWDep")) + cryfwrd
                ElseIf drow("SHWDep") > 0 Then
                    drow("SHWDep") = CDbl(drow("SHWDep")) + cryfwrd
                ElseIf drow("UTGDep") > 0 Then
                    drow("UTGDep") = CDbl(drow("UTGDep")) + cryfwrd
                ElseIf drow("TPGDep") > 0 Then
                    drow("TPGDep") = CDbl(drow("TPGDep")) + cryfwrd
                End If
            End If
            'drow("TPGDep") = CDbl(CDbl(drow("TPGDep")) + (CDbl(drow("MonthlyAmount")) - (CDbl(drow("HODep")) + CDbl(drow("FWDep")) + CDbl(drow("SHWDep")) + CDbl(drow("UTGDep")) + CDbl(drow("TPGDep"))))).ToString("#0.000")
        Next

        sqlCon.Close()
        CalculateDepreciation = ds
    End Function
    Public Function ProjectDepreciation(ByVal GroupID As Integer, ByVal LocationID As Integer, ByVal ProcessDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        ProcessDate = ValidDate(ProcessDate)

        If LocationID = 0 Then
            If GroupID = 0 Then
                strsql = "select AssetID,AssetName,AssetNumber,PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "DATEDIFF(Month, PurchaseDate,'" & ProcessDate & "') as TotalLife, PurchaseAmount/LifeInMonths MonthlyAmount from Asset " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' " & _
                        "order by AssetNumber"
            Else
                strsql = "select AssetID,AssetName,AssetNumber,PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "DATEDIFF(Month, PurchaseDate,'" & ProcessDate & "') as TotalLife,PurchaseAmount/LifeInMonths MonthlyAmount from Asset " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' and GroupID=" & GroupID & " " & _
                        "order by  AssetNumber"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select AssetID,AssetName,AssetNumber,PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "DATEDIFF(Month, PurchaseDate,'" & ProcessDate & "') as TotalLife,PurchaseAmount/LifeInMonths MonthlyAmount from Asset a " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' " & _
                        "and DptID=" & LocationID & " order by  AssetNumber"
            Else
                strsql = "select AssetID,AssetName,AssetNumber,PurchaseDate,AssetStatus," & _
                        "PurchaseAmount,LifeInMonths,DprcnPercent,CurrentValue," & _
                        "DATEDIFF(Month, PurchaseDate,'" & ProcessDate & "') as TotalLife,PurchaseAmount/LifeInMonths MonthlyAmount from Asset " & _
                        "where CurrentValue>0 and ltrim(rtrim(AssetStatus))='ACTIVE' and GroupID=" & GroupID & " " & _
                        "and DptID=" & LocationID & " order by  AssetNumber"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ASSET")
        Try
            da.Fill(ds, "ASSET")
            Dim drow As DataRow
            For Each drow In ds.Tables(0).Rows
                If Decimal.Parse(drow("CurrentValue")) > 0 Then
                    If CInt(drow("LifeInMonths")) <= CInt(Replace(drow("TotalLife"), "-", "")) Then
                        drow("CurrentValue") = "0.000"
                    Else
                        Dim AccDprn As Decimal = 0.0
                        Dim PurhAmt As Decimal = 0.0
                        Dim NewCvalue As Decimal = 0.0
                        Dim ProjectedLife As Integer = 0
                        ProjectedLife = Decimal.Parse(Replace(drow("TotalLife"), "-", ""))
                        PurhAmt = drow("PurchaseAmount")
                        AccDprn = Decimal.Parse(drow("MonthlyAmount")) * ProjectedLife
                        NewCvalue = PurhAmt - AccDprn
                        If AccDprn > PurhAmt Then
                            NewCvalue = 0.0
                        End If
                        drow("CurrentValue") = NewCvalue
                    End If
                End If
            Next
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        ProjectDepreciation = ds
    End Function
    Public Sub InsertAssetPurchase(ByVal AssetID As Integer, ByVal LocID As Integer, _
           ByVal PurchDate As String, ByVal Remarks As String, ByVal InvoiceNo As String, ByVal InvAmount As Decimal, ByVal SupName As String, _
           ByVal T1 As Decimal, ByVal T2 As Decimal, ByVal T3 As Decimal, ByVal T4 As Decimal, ByVal T5 As Decimal, ByVal tbLife As Integer)
        Try


            Dim sqlCon As SqlConnection = New SqlConnection(strCon)
            Dim strsql As String

            strsql = "Insert into AssetPurchase (AssetID,LocationID," & _
                    "PurchDate,Remarks,InvoiceNo,InvoiceAmount,SupplierName) " & _
                    "values(" & AssetID & "," & LocID & "," & _
                    "'" & ValidDate(PurchDate) & "','" & Remarks & "','" & InvoiceNo & "','" & PrefixInteger(InvAmount) & "','" & SupName & "') "
            sqlCon.Open()
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            sqlCmd.ExecuteNonQuery()
            strsql = "Update Asset set DptID=" & LocID & ",PurchaseDate='" & ValidDate(PurchDate) & "',PurchaseAmount='" & PrefixInteger(InvAmount) & "'," & "CurrentValue='" & PrefixInteger(InvAmount) & "'," & _
                    "InvoiceNumber='" & InvoiceNo & "',LifeInMonths=" & tbLife & ",Remarks='" & Remarks & "',HOPercentage=" & CDbl(T1) & ",FWPercentage=" & CDbl(T2) & _
                    ",SHWPercentage=" & CDbl(T3) & ",UTGPercentage=" & CDbl(T4) & ",TPGPercentage=" & CDbl(T5) & " where AssetID=" & AssetID
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
            ' GL posting of purchase
            Dim ds As DataSet
            ds = GetAssetAccounts(AssetID)
            Dim drow As DataRow
            Dim FaAcm, FaAcs, ExAcm, ExAcs, AdAcm, AdAcs, PCAcm, PCAcs As String
            For Each drow In ds.Tables(0).Rows
                FaAcm = drow("Fam")
                FaAcs = drow("Fas")
                AdAcm = drow("Adm")
                AdAcs = drow("Ads")
                ExAcm = drow("Exm")
                ExAcs = drow("Exs")
                PCAcm = drow("Pcm")
                PCAcs = drow("Pcs")
            Next
            Dim GLdata As New GLdataLogic
            Dim TrxNum As String = GLdata.GetNextTrxNumber(CompanyID, "JV")
            Dim GlDesc As String = ""
            GlDesc = InvoiceNo + ":" + SupName
            If GlDesc.Length > 50 Then
                GlDesc = GlDesc.Substring(0, 49)
            End If
            strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                    "TrxRefer,TrxNumber) " & _
                    "Values ('" & CompanyID & "','" & FaAcm & "','" & FaAcs & "'," & _
                    "'" & ValidDate(PurchDate) & "','" & PrefixInteger(InvAmount) & "','ASSET','" + GlDesc + "'," & _
                    "'" & TrxNum & "')"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
            strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                    "TrxRefer,TrxNumber) " & _
                    "Values ('" & CompanyID & "','" & PCAcm & "','" & PCAcs & "'," & _
                    "'" & ValidDate(PurchDate) & "','" & 0 - PrefixInteger(InvAmount) & "','ASSET','" + GlDesc + "'," & _
                    "'" & TrxNum & "')"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

    Public Sub InsertAssetSale(ByVal AssetID As Integer, _
        ByVal SaleDate As String, ByVal Remarks As String, ByVal InvoiceNo As String, ByVal InvAmount As Decimal, ByVal CusName As String, ByVal BookVal As Decimal)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        strsql = "Insert into AssetSale (AssetID," & _
                "SaleDate,Remarks,VoucherNo,SaleAmount,CustomerName,BookValue) " & _
                "values(" & AssetID & "," & _
                "'" & ValidDate(SaleDate) & "','" & Remarks & "','" & InvoiceNo & "','" & PrefixInteger(InvAmount) & "','" & CusName & "'," & BookVal & " ) "
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        strsql = "Update Asset set AssetStatus='SOLD' where AssetID=" & AssetID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        'strsql = "Update Asset set CurrentValue = 0.000 where AssetID=" & AssetID
        'sqlCmd.CommandText = strsql
        'sqlCmd.ExecuteNonQuery()
        ' GL posting of sales
        Dim ds As DataSet
        ds = GetAssetAccounts(AssetID)
        Dim drow As DataRow
        Dim FaAcm, FaAcs, ExAcm, ExAcs, AdAcm, AdAcs, PCAcm, PCAcs As String
        For Each drow In ds.Tables(0).Rows
            FaAcm = drow("Fam")
            FaAcs = drow("Fas")
            AdAcm = drow("Adm")
            AdAcs = drow("Ads")
            ExAcm = 2500
            ExAcs = drow("Exs")
            PCAcm = drow("Pcm")
            PCAcs = drow("Pcs")
        Next
        Dim ds2 As DataSet
        ds2 = GetAssetDetails(AssetID)
        Dim PurchAmt, BookValue, AccDprn, ProfitLoss As Decimal
        For Each drow In ds2.Tables(0).Rows
            PurchAmt = drow("PurchaseAmount")
            BookValue = drow("CurrentValue")
        Next
        AccDprn = PurchAmt - BookValue
        ProfitLoss = BookValue - InvAmount
        Dim GLdata As New GLdataLogic
        'Dim TrxNum As Integer = GLdata.GetNextTrxNumber(CompanyID, "JV")
        Dim TrxNum As String = GLdata.GetNextTrxNumber(CompanyID, CStr("JV"))
        strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                "TrxRefer,TrxNumber) " & _
                "Values ('" & CompanyID & "','" & FaAcm & "','" & FaAcs & "'," & _
                "'" & ValidDate(SaleDate) & "','" & 0 - PrefixInteger(PurchAmt) & "','ASSET','INV:" + InvoiceNo + " CUS:" + CusName + "'," & _
                "'" & TrxNum & "')"
        sqlCmd.CommandText = strsql
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
        strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                "TrxRefer,TrxNumber) " & _
                "Values ('" & CompanyID & "','" & AdAcm & "','" & AdAcs & "'," & _
                "'" & ValidDate(SaleDate) & "','" & PrefixInteger(AccDprn) & "','ASSET','INV:" + InvoiceNo + " CUS:" + CusName + "'," & _
                "'" & TrxNum & "')"
        sqlCmd.CommandText = strsql
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
        'sqlCmd.ExecuteNonQuery()
        strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                "TrxRefer,TrxNumber) " & _
                "Values ('" & CompanyID & "','" & PCAcm & "','" & PCAcs & "'," & _
                "'" & ValidDate(SaleDate) & "','" & PrefixInteger(InvAmount) & "','ASSET','INV:" + InvoiceNo + " CUS:" + CusName + "'," & _
                "'" & TrxNum & "')"
        sqlCmd.CommandText = strsql
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
        'sqlCmd.ExecuteNonQuery()
        If ProfitLoss > 0 Then
            strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                    "TrxRefer,TrxNumber) " & _
                    "Values ('" & CompanyID & "','" & ExAcm & "','" & ExAcs & "'," & _
                    "'" & ValidDate(SaleDate) & "','" & PrefixInteger(ProfitLoss) & "','ASSET','INV:" + InvoiceNo + " LOSS'," & _
                    "'" & TrxNum & "')"
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString())
            End Try
            'sqlCmd.ExecuteNonQuery()
        End If
        If ProfitLoss < 0 Then
            strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                    "TrxRefer,TrxNumber) " & _
                    "Values ('" & CompanyID & "','" & ExAcm & "','" & ExAcs & "'," & _
                    "'" & ValidDate(SaleDate) & "','" & PrefixInteger(ProfitLoss) & "','ASSET','INV:" + InvoiceNo + " PROFIT'," & _
                    "'" & TrxNum & "')"
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString())
            End Try
            'sqlCmd.ExecuteNonQuery()
        End If
        'sqlCon.Open()
        Dim strsql1 As String
        strsql1 = "Update Asset set CurrentValue = 0.000 where AssetID=" & AssetID
        Dim sqlCmd1 As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd1.CommandText = strsql1
        sqlCmd1.ExecuteNonQuery()

        sqlCon.Close()
    End Sub
    Public Sub InsertAssetScrap(ByVal AssetID As Integer, _
     ByVal SaleDate As String, ByVal Remarks As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        strsql = "Insert into AssetScrap (AssetID," & _
                "ScrapDate,Remarks) " & _
                "values(" & AssetID & "," & _
                "'" & ValidDate(SaleDate) & "','" & Remarks & "') "
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        strsql = "Update Asset set AssetStatus='SCRAP' where AssetID=" & AssetID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        '' GL posting of scrap
        'Dim ds As DataSet
        'ds = GetAssetAccounts(AssetID)
        'Dim drow As DataRow
        'Dim FaAcm, FaAcs, ExAcm, ExAcs, AdAcm, AdAcs, PCAcm, PCAcs As String
        'For Each drow In ds.Tables(0).Rows
        '    FaAcm = drow("Fam")
        '    FaAcs = drow("Fas")
        '    AdAcm = drow("Adm")
        '    AdAcs = drow("Ads")
        '    ExAcm = drow("Exm")
        '    ExAcs = drow("Exs")
        '    PCAcm = drow("Pcm")
        '    PCAcs = drow("Pcs")
        'Next
        'Dim GLdata As New GLdataLogic
        'Dim TrxNum As Integer = GLdata.GetNextTrxNumber(CompanyID, "JV")
        'strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
        '        "TrxRefer,TrxNumber) " & _
        '        "Values ('" & CompanyID & "','" & FaAcm & "','" & FaAcs & "'," & _
        '        "'" & ValidDate(SaleDate) & "','" & 0 - PrefixInteger(InvAmount) & "','ASSET','INV:" + InvoiceNo + " CUS:" + CusName + "'," & _
        '        "'" & TrxNum & "')"
        'sqlCmd.CommandText = strsql
        'sqlCmd.ExecuteNonQuery()
        'strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
        '        "TrxRefer,TrxNumber) " & _
        '        "Values ('" & CompanyID & "','" & PCAcm & "','" & PCAcs & "'," & _
        '        "'" & ValidDate(SaleDate) & "','" & PrefixInteger(InvAmount) & "','ASSET','INV:" + InvoiceNo + " CUS:" + CusName + "'," & _
        '        "'" & TrxNum & "')"
        'sqlCmd.CommandText = strsql
        'sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub UpdateCustomerDetails(ByVal CusID As Integer, ByVal CusCode As String, ByVal GroupID As Integer, ByVal CusName As String, _
                ByVal Adr1 As String, ByVal Adr2 As String, ByVal Dadr1 As String, ByVal Dadr2 As String, _
                ByVal Email As String, ByVal Website As String, ByVal Phone As String, _
                ByVal Fax As String, ByVal Contact As String, ByVal SalesmanID As Integer, ByVal Blocked As Integer, ByVal CusXml As String, ByVal ContractCode As String, ByVal ContractAmt As Decimal, ByVal StartDate As Date, ByVal EndDate As Date, ByVal ActStartDate As Date, ByVal ContractInfo As String)

        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        Dim Start, EndDt, Act As String

        Start = StartDate.ToString("yyyy-MM-dd")
        EndDt = EndDate.ToString("yyyy-MM-dd")
        Act = ActStartDate.ToString("yyyy-MM-dd")

        strsql = "Update Customer set CusGroupID=" & GroupID & "," & _
                "CusCode='" & CusCode & "',CusName='" & CusName & "',CusAdr1='" & Adr1 & "',CusAdr2='" & Adr2 & "',CusDadr1='" & Dadr1 & "',CusDadr2='" & Dadr2 & "'," & _
                "CusEmail='" & Email & "',CusWebUrl='" & Website & "',CusTelephone='" & Phone & "'," & _
                "CusFax='" & Fax & "',CusContact='" & Contact & "',SalesmanID='" & SalesmanID & "',Blocked='" & Blocked & "',Trfrd='N' ,ContractType='" & ContractCode & "',ContractAmt=" & ContractAmt & ",StartDate='" & Start & "',EndDate='" & EndDt & "',ActStartDate='" & Act & "',ContractInfo='" & ContractInfo & "'" & _
                " Where CusID=" & CusID
        sqlCon.Open()
        sqlCon2.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CusXml)
        Dim IncludeCus As Boolean
        Dim CrLimit, CrPeriod As String
        Dim ThisCompany As Integer
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            IncludeCus = nodeList.Item(i).ChildNodes(1).InnerXml()
            ThisCompany = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            CrLimit = nodeList.Item(i).ChildNodes(2).InnerXml()
            CrPeriod = nodeList.Item(i).ChildNodes(3).InnerXml()

            If CrLimit = "" Then
                CrLimit = "0"
            End If

            If CrPeriod = "" Then
                CrPeriod = "0"
            End If

            If IncludeCus = True Then
                strsql = "Select count(*) from CompanyCustomer where CompanyID=" & ThisCompany & " and CusID=" & CusID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)

                If sqlCmd2.ExecuteScalar = 0 Then
                    strsql = "Insert into CompanyCustomer (CusID,CompanyID,CreditLimit,CrPeriod)  values(" & CusID & "," & ThisCompany & ",'" & CrLimit & "', " & CrPeriod & "')"
                Else
                    strsql = "Update CompanyCustomer set CreditLimit='" & CrLimit & "' ,CrPeriod='" & CrPeriod & "'  where CusID=" & CusID & " and CompanyID=" & ThisCompany
                End If

                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            Else
                strsql = "Select count(*) from CompanyCustomer where CompanyID=" & ThisCompany & " and CusID=" & CusID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
                If sqlCmd2.ExecuteScalar > 0 Then
                    strsql = "Delete from CompanyCustomer " & _
                            "where CusID=" & CusID & " and CompanyID=" & ThisCompany
                    sqlCmd.CommandText = strsql
                    sqlCmd.ExecuteNonQuery()
                End If
            End If
        Next i
        sqlCon.Close()
    End Sub
    Public Sub UpdateCustomerCompanyBrandDiscount(ByVal CompanyID As Integer, ByVal CusID As Integer, ByVal CusXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        sqlCon.Open()
        sqlCon2.Open()
        Dim strsql As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CusXml)
        Dim Discount As String
        Dim GroupID As Integer
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            GroupID = nodeList.Item(i).ChildNodes(0).InnerXml()
            Discount = nodeList.Item(i).ChildNodes(1).InnerXml()
            strsql = "Select count(*) from CustomerCompanyBrandDiscount where CompanyID=" & CompanyID & " and CusID=" & CusID & " and BrandID=" & GroupID
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
            If sqlCmd2.ExecuteScalar = 0 Then
                strsql = "Insert into CustomerCompanyBrandDiscount (CusID,CompanyID,BrandID,DiscountPercent) " & _
                        "values(" & CusID & "," & CompanyID & "," & GroupID & ",'" & Discount & "')"
            Else
                strsql = "Update CustomerCompanyBrandDiscount set DiscountPercent='" & Discount & "' " & _
                        "where CusID=" & CusID & " and CompanyID=" & CompanyID & " and BrandID=" & GroupID
            End If
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        sqlCon.Close()
        sqlCon2.Close()
    End Sub
    Public Sub InsertError(ByVal ErrModule As String, ByVal ErrLine As String, ByVal ErrNumber As String, ByVal ErrDescription As String)
        'On Error Resume Next
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into ErrorLog (ErrModule,ErrLine,ErrNumber,ErrDescription) " & _
                "values('" & ErrModule & "','" & ErrLine & "','" & ErrNumber & "','" & ErrDescription.Replace("'", "") & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Private Function PrefixInteger(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Return strVal.Replace(",", "")
        End If
    End Function
    Private Function ValidDate(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Dim dd As String
            Dim mm As String
            Dim yyyy As String
            dd = strVal.Substring(0, 2)
            mm = strVal.Substring(3, 2)
            yyyy = strVal.Substring(6, 4)
            strVal = mm & "/" & dd & "/" & yyyy
            Return strVal
        End If
    End Function
    Public Function GetSupplierEmail(ByVal SupplierID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select SupEmail from Supplier where SupID=" & SupplierID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = CStr(rdr("SupEmail"))
        End While
        Dim nlen As Integer
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function InsertPCPtargetedEmail(ByVal MailSubject As String, ByVal MailText As String, _
            ByVal LeftPhotoPath As String, ByVal MiddlePhotoPath As String, ByVal RightPhotoPath As String, _
            ByVal KuwaitFlag As Integer, ByVal ArabFlag As Integer, ByVal EuroFlag As Integer, _
            ByVal AsianFlag As Integer, ByVal Group1Flag As Integer, ByVal Group2Flag As Integer, _
            ByVal Group3Flag As Integer, ByVal Group4Flag As Integer, ByVal Group5Flag As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            'Dim oImg As FileStream
            'Dim oBinaryReader As BinaryReader
            'Dim oImgByteArray As Byte()
            'Dim LeftParam, MiddleParam, RightParam As SqlParameter
            'If LeftPhotoPath.Trim <> "" Then
            '    oImg = New FileStream(LeftPhotoPath, FileMode.Open, FileAccess.Read)
            '    oBinaryReader = New BinaryReader(oImg)
            '    oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
            '    LeftParam = New SqlParameter("@LeftImage", SqlDbType.Image, oImg.Length)
            '    LeftParam.Value = oImgByteArray
            '    oBinaryReader.Close()
            '    oImg.Close()
            'Else
            '    LeftParam = New SqlParameter("@LeftImage", SqlDbType.Image)
            '    LeftParam.Value = System.DBNull.Value
            'End If
            'If MiddlePhotoPath.Trim <> "" Then
            '    oImg = New FileStream(MiddlePhotoPath, FileMode.Open, FileAccess.Read)
            '    oBinaryReader = New BinaryReader(oImg)
            '    oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
            '    MiddleParam = New SqlParameter("@MiddleImage", SqlDbType.Image, oImg.Length)
            '    MiddleParam.Value = oImgByteArray
            '    oBinaryReader.Close()
            '    oImg.Close()
            'Else
            '    MiddleParam = New SqlParameter("@MiddleImage", SqlDbType.Image)
            '    MiddleParam.Value = System.DBNull.Value
            'End If
            'If RightPhotoPath.Trim <> "" Then
            '    oImg = New FileStream(RightPhotoPath, FileMode.Open, FileAccess.Read)
            '    oBinaryReader = New BinaryReader(oImg)
            '    oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
            '    RightParam = New SqlParameter("@RightImage", SqlDbType.Image, oImg.Length)
            '    RightParam.Value = oImgByteArray
            '    oBinaryReader.Close()
            '    oImg.Close()
            'Else
            '    RightParam = New SqlParameter("@RightImage", SqlDbType.Image)
            '    RightParam.Value = System.DBNull.Value
            'End If
            'sqlCmd.Parameters.Add(LeftParam)
            'sqlCmd.Parameters.Add(MiddleParam)
            'sqlCmd.Parameters.Add(RightParam)
            Dim EmDs As DataSet
            EmDs = GetPcpMembersForTargetedEmail(KuwaitFlag, ArabFlag, EuroFlag, AsianFlag, _
                    Group1Flag, Group2Flag, Group3Flag, Group4Flag, Group5Flag)
            Dim TotRec As Integer
            TotRec = EmDs.Tables(0).Rows.Count
            Dim TotBCC As Integer = 1
            If TotRec > 99 Then
                TotBCC = TotRec / 99
            End If
            Dim drow As DataRow
            Dim BCC, CusName As String
            Dim mcount As Integer
            Dim lArray(), mArray(), rArray() As String
            If LeftPhotoPath.Trim <> "" Then
                lArray = LeftPhotoPath.Split("\")
                LeftPhotoPath = lArray(UBound(lArray))
            End If
            If MiddlePhotoPath.Trim <> "" Then
                mArray = MiddlePhotoPath.Split("\")
                MiddlePhotoPath = mArray(UBound(mArray))
            End If
            If RightPhotoPath.Trim <> "" Then
                rArray = RightPhotoPath.Split("\")
                RightPhotoPath = rArray(UBound(rArray))
            End If

            For Each drow In EmDs.Tables(0).Rows
                BCC = drow("Email")
                CusName = drow("CusName")
                strsql = "Insert into PcpMassEmailHistory(MailSubject,MailText,MailKuwaiti,MailArab," & _
                    "MailWestern,MailAsian,MailPhotoLeft,MailPhotoMiddle,MailPhotoRight,MailToName,MailBCCaddress," & _
                    "MailGroup1,MailGroup2,MailGroup3,MailGroup4,MailGroup5) " & _
                    "Values ('" & MailSubject & "','" & MailText & "'," & KuwaitFlag & "," & _
                    ArabFlag & "," & EuroFlag & "," & AsianFlag & "," & _
                    LeftPhotoPath & "','" & MiddlePhotoPath & "','" & RightPhotoPath & "','" & CusName & "','" & BCC & "'," & Group1Flag & "," & Group2Flag & "," & Group3Flag & "," & Group4Flag & "," & Group5Flag & ")"
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            Next
            'For Each drow In EmDs.Tables(0).Rows
            '    mcount += 1
            '    Select Case mcount
            '        Case Is < 100
            '            If BCC1 = "" Then
            '                BCC1 += drow("Email")
            '            Else
            '                BCC1 += ";" + drow("Email")
            '            End If
            '        Case Is < 200
            '            If BCC2 = "" Then
            '                BCC2 += drow("Email")
            '            Else
            '                BCC2 += ";" + drow("Email")
            '            End If
            '        Case Is < 300
            '            If BCC3 = "" Then
            '                BCC3 += drow("Email")
            '            Else
            '                BCC3 += ";" + drow("Email")
            '            End If
            '        Case Is < 400
            '            If BCC4 = "" Then
            '                BCC4 += drow("Email")
            '            Else
            '                BCC4 += ";" + drow("Email")
            '            End If
            '        Case Is < 500
            '            If BCC5 = "" Then
            '                BCC5 += drow("Email")
            '            Else
            '                BCC5 += ";" + drow("Email")
            '            End If
            '        Case Is < 600
            '            If BCC6 = "" Then
            '                BCC6 += drow("Email")
            '            Else
            '                BCC6 += ";" + drow("Email")
            '            End If
            '        Case Is < 700
            '            If BCC7 = "" Then
            '                BCC7 += drow("Email")
            '            Else
            '                BCC7 += ";" + drow("Email")
            '            End If
            '        Case Is < 800
            '            If BCC8 = "" Then
            '                BCC8 += drow("Email")
            '            Else
            '                BCC8 += ";" + drow("Email")
            '            End If
            '        Case Is < 900
            '            If BCC9 = "" Then
            '                BCC9 += drow("Email")
            '            Else
            '                BCC9 += ";" + drow("Email")
            '            End If
            '        Case Is < 1000
            '            If BCC10 = "" Then
            '                BCC10 += drow("Email")
            '            Else
            '                BCC10 += ";" + drow("Email")
            '            End If
            '    End Select
            'Next
            'Dim n As Integer
            'For n = 1 To TotBCC
            '    Dim BCC As String = ""
            '    Select Case n
            '        Case Is = 1
            '            BCC = BCC1
            '        Case Is = 2
            '            BCC = BCC2
            '        Case Is = 3
            '            BCC = BCC3
            '        Case Is = 4
            '            BCC = BCC4
            '        Case Is = 5
            '            BCC = BCC5
            '        Case Is = 6
            '            BCC = BCC6
            '        Case Is = 7
            '            BCC = BCC7
            '        Case Is = 8
            '            BCC = BCC8
            '        Case Is = 9
            '            BCC = BCC9
            '        Case Is = 10
            '            BCC = BCC10
            '    End Select
            '    strsql = "Insert into PcpMassEmailHistory(MailSubject,MailText,MailKuwaiti,MailArab," & _
            '        "MailWestern,MailAsian,MailPhotoLeft,MailPhotoMiddle,MailPhotoRight,MailBCCaddress," & _
            '        "MailGroup1,MailGroup2,MailGroup3,MailGroup4,MailGroup5) " & _
            '        "Values ('" & MailSubject & "','" & MailText & "'," & KuwaitFlag & "," & _
            '        ArabFlag & "," & EuroFlag & "," & AsianFlag & "," & _
            '        "@LeftImage,@MiddleImage,@RightImage,'" & BCC & "'," & Group1Flag & "," & Group2Flag & "," & Group3Flag & "," & Group4Flag & "," & Group5Flag & ")"
            '    sqlCmd.CommandText = strsql
            '    sqlCmd.ExecuteNonQuery()
            '    If n = 10 Then
            '        Exit For
            '    End If
            'Next
        Catch ex As Exception
            Return "ERROR"
        End Try
        sqlCon.Close()
        Return "OK"
    End Function
    Public Function InsertPCPmassEmail(ByVal MailSubject As String, ByVal MailText As String, _
                ByVal LeftPhotoPath As String, ByVal MiddlePhotoPath As String, ByVal RightPhotoPath As String, _
                ByVal KuwaitFlag As Integer, ByVal ArabFlag As Integer, ByVal EuroFlag As Integer, _
                ByVal AsianFlag As Integer, ByVal NonFlag As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            'Dim oImg As FileStream
            'Dim oBinaryReader As BinaryReader
            'Dim oImgByteArray As Byte()
            'Dim LeftParam, MiddleParam, RightParam As SqlParameter
            'If LeftPhotoPath.Trim <> "" Then
            '    oImg = New FileStream(LeftPhotoPath, FileMode.Open, FileAccess.Read)
            '    oBinaryReader = New BinaryReader(oImg)
            '    oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
            '    LeftParam = New SqlParameter("@LeftImage", SqlDbType.Image, oImg.Length)
            '    LeftParam.Value = oImgByteArray
            '    oBinaryReader.Close()
            '    oImg.Close()
            'Else
            '    LeftParam = New SqlParameter("@LeftImage", SqlDbType.Image)
            '    LeftParam.Value = System.DBNull.Value
            'End If
            'If MiddlePhotoPath.Trim <> "" Then
            '    oImg = New FileStream(MiddlePhotoPath, FileMode.Open, FileAccess.Read)
            '    oBinaryReader = New BinaryReader(oImg)
            '    oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
            '    MiddleParam = New SqlParameter("@MiddleImage", SqlDbType.Image, oImg.Length)
            '    MiddleParam.Value = oImgByteArray
            '    oBinaryReader.Close()
            '    oImg.Close()
            'Else
            '    MiddleParam = New SqlParameter("@MiddleImage", SqlDbType.Image)
            '    MiddleParam.Value = System.DBNull.Value
            'End If
            'If RightPhotoPath.Trim <> "" Then
            '    oImg = New FileStream(RightPhotoPath, FileMode.Open, FileAccess.Read)
            '    oBinaryReader = New BinaryReader(oImg)
            '    oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
            '    RightParam = New SqlParameter("@RightImage", SqlDbType.Image, oImg.Length)
            '    RightParam.Value = oImgByteArray
            '    oBinaryReader.Close()
            '    oImg.Close()
            'Else
            '    RightParam = New SqlParameter("@RightImage", SqlDbType.Image)
            '    RightParam.Value = System.DBNull.Value
            'End If
            'sqlCmd.Parameters.Add(LeftParam)
            'sqlCmd.Parameters.Add(MiddleParam)
            'sqlCmd.Parameters.Add(RightParam)
            Dim EmDs As DataSet
            EmDs = GetPcpMembersForEventEmail(KuwaitFlag, ArabFlag, EuroFlag, AsianFlag, NonFlag)
            Dim TotRec As Integer
            TotRec = EmDs.Tables(0).Rows.Count
            Dim TotBCC As Integer = 1
            If TotRec > 99 Then
                TotBCC = TotRec / 99
            End If
            Dim drow As DataRow
            Dim BCC1, BCC2, BCC3, BCC4, BCC5, BCC6, BCC7, BCC8, BCC9, BCC10 As String
            Dim BCC, CusName As String
            Dim mcount As Integer
            Dim lArray(), mArray(), rArray() As String
            If LeftPhotoPath.Trim <> "" Then
                lArray = LeftPhotoPath.Split("\")
                LeftPhotoPath = lArray(UBound(lArray))
            End If
            If MiddlePhotoPath.Trim <> "" Then
                mArray = MiddlePhotoPath.Split("\")
                MiddlePhotoPath = mArray(UBound(mArray))
            End If
            If RightPhotoPath.Trim <> "" Then
                rArray = RightPhotoPath.Split("\")
                RightPhotoPath = rArray(UBound(rArray))
            End If

            For Each drow In EmDs.Tables(0).Rows
                BCC = drow("Email")
                CusName = drow("CusName")
                strsql = "Insert into PcpMassEmailHistory(MailSubject,MailText,MailKuwaiti,MailArab," & _
                    "MailWestern,MailAsian,MailNonMember,MailPhotoLeft,MailPhotoMiddle,MailPhotoRight,MailToName,MailBCCaddress) " & _
                    "Values ('" & MailSubject & "','" & MailText & "'," & KuwaitFlag & "," & _
                    ArabFlag & "," & EuroFlag & "," & AsianFlag & "," & _
                    NonFlag & ",'" & LeftPhotoPath & "','" & MiddlePhotoPath & "','" & RightPhotoPath & "','" & CusName & "','" & BCC & "')"
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            Next
        Catch ex As Exception
            Return "ERROR"
        End Try
        sqlCon.Close()
        Return "OK"
    End Function

    Public Function GetAllProductBarcode() As DataSet
        Try
            Dim sqlCon As SqlConnection = New SqlConnection(strCon)
            Dim strsql As String
            strsql = " Select ProductId,ProductBarcode from Barcode "

            sqlCon.Open()
            Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
            Dim ds As New DataSet("Barcode")
            da.Fill(ds, "Barcode")
            sqlCon.Close()
            Return ds

        Catch ex As Exception
            MsgBox(ex.ToString, "SERPS")
        End Try
    End Function

    Public Function GetAllProductBarcodeWithoutCriteria() As DataSet
        Try
            Dim sqlCon As SqlConnection = New SqlConnection(strCon)
            Dim strsql As String
            strsql = "Select b.ProductId,ProductCode,ManufProductCode,ShortdescriptionE Description,ProductBarcode Barcode from Barcode b , Product P where p.productid=b.productid order by b.ProductId"

            sqlCon.Open()
            Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
            Dim ds As New DataSet("ProductBarcode")
            da.Fill(ds, "ProductBarcode")
            sqlCon.Close()
            Return ds

        Catch ex As Exception
            MsgBox(ex.ToString, "SERPS")
        End Try
    End Function


End Class
